Re: slow select in big table

rafalak <rafalak(at)gmail(dot)com> writes:
> QUERY PLAN without changes
> Aggregate (cost=98018.96..98018.97 rows=1 width=4) (actual
> time=64049.326..64049.328 rows=1 loops=1)
> -> Bitmap Heap Scan on tbl_photos_keywords (cost=533.23..97940.02
> rows=31577 width=4) (actual time=157.787..63905.939 rows=119154
> loops=1)
> Recheck Cond: (keyword_id = 14)
> -> Bitmap Index Scan on keyword_id (cost=0.00..525.33
> rows=31577 width=0) (actual time=120.876..120.876 rows=119154 loops=1)
> Index Cond: (keyword_id = 14)

> enable_seqscan = off

This is a bad idea (and did not affect your plan anyway)

> random_page_cost = 1.0

This might or might not be a good idea, depending on whether your
database fits in RAM or not.

> QUERY PLAN with changes
> Aggregate (cost=30546.30..30546.31 rows=1 width=4) (actual
> time=1710.100..1710.102 rows=1 loops=1)
> -> Index Scan using keyword_id on tbl_photos_keywords
> (cost=0.00..30467.36 rows=31577 width=4) (actual time=0.150..1573.843
> rows=119154 loops=1)
> Index Cond: (keyword_id = 14)

It's hard to believe that this plan actually beats the other one on a
consistent basis; and especially not by that much. I think what really
happened here is that the data was all cached in the second test,
because the first test read it all in already. You need to test both
cases (cached and not) to get a clearer picture of what you're doing.

regards, tom lane