By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,809 Members | 1,999 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,809 IT Pros & Developers. It's quick & easy.

table configuration tweak for performance gain.

P: n/a
Hi all,
I was hoping someone might be able to set me straight
so that I can get some select performance improvements.

The table in question has several thousand rows currently,
planning several million, it has a date time stamp column.
All selects will be on ranges of the date time column.

I created an index on the dt column, ran ANALYSE then,
EXPLAIN for some queries.
The returned plan was always sequential search.
The SELECT time also indicates sequential search.

Is there anything I can do to improve SELECT performance
on what is essentially an ordered table?

Thanks in advance.
Allan

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Mon, Nov 15, 2004 at 04:25:59PM +1100, Harvey, Allan AC wrote:
The table in question has several thousand rows currently,
planning several million, it has a date time stamp column.
All selects will be on ranges of the date time column.

I created an index on the dt column, ran ANALYSE then,
EXPLAIN for some queries.
The returned plan was always sequential search.
The SELECT time also indicates sequential search.

Is there anything I can do to improve SELECT performance
on what is essentially an ordered table?


Could you post a query and the EXPLAIN ANALYZE output? We could
probably give better advice if we could see what's happening.

Have you experimented with lowering random_page_cost?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #2

P: n/a
Michael Fuhr <mi**@fuhr.org> writes:
On Mon, Nov 15, 2004 at 04:25:59PM +1100, Harvey, Allan AC wrote:
I created an index on the dt column, ran ANALYSE then,
EXPLAIN for some queries.
The returned plan was always sequential search.
Could you post a query and the EXPLAIN ANALYZE output? We could
probably give better advice if we could see what's happening.


Also, let's see EXPLAIN ANALYZE results after setting enable_seqscan to
OFF. If that doesn't force it into an indexscan, then you have got
more fundamental issues (perhaps a datatype mismatch). Note that I
don't recommend turning off enable_seqscan as a production solution;
but it's a useful tool for debugging.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.