Re: Large # of rows in query extremely slow, not using

On Thu, 16 Sep 2004 20:51:11 -0500, Stephen Crowley
<stephen(dot)crowley(at)gmail(dot)com> wrote:
>explain analyze select * from history where date='2004-09-07' and
>stock='ORCL' LIMIT 10;

>" -> Index Scan using island_history_date_stock_time on
>island_history (cost=0.00..183099.72 rows=102166 width=83) (actual
>time=1612.000..1702.000 rows=10 loops=1)"
^^
LIMIT 10 hides what would be the most interesting info here. I don't
believe that
EXPLAIN ANALYSE SELECT * FROM history WHERE ...
consumes lots of memory. Please try it.

And when you post the results please include your Postgres version, some
info about hardware and OS, and your non-default settings, especially
random_page_cost and effective_cache_size.

May I guess that the correlation of the physical order of tuples in your
table to the contents of the date column is pretty good (examine
correlation in pg_stats) and that island_history_date_stock_time is a
3-column index?

It is well known that the optimizer overestimates the cost of index
scans in those situations. This can be compensated to a certain degree
by increasing effective_cache_size and/or decreasing random_page_cost
(which might harm other planner decisions).

You could also try
CREATE INDEX history_date_stock ON history("date", stock);

This will slow down INSERTs and UPDATEs, though.

Servus
Manfred