table configuration tweak for performance gain.

[Harvey, Allan AC]

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

[Michael Fuhr]

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

[Tom Lane]

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