Search Postgresql Archives

Re: slow select in big table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



rafalak <rafalak@xxxxxxxxx> 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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux