Re: Why should such a simple query over indexed columns be so slow?

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

 





On Mon, Jan 30, 2012 at 17:35, Alessandro Gagliardi <alessandro@xxxxxxxx> wrote:
Well that was a lot faster:

"HashAggregate  (cost=156301.82..156301.83 rows=2 width=26) (actual time=2692.806..2692.807 rows=2 loops=1)"
"  ->  Bitmap Heap Scan on blocks  (cost=14810.54..155828.95 rows=472871 width=26) (actual time=289.828..1593.893 rows=575186 loops=1)"
"        Recheck Cond: (created > '2012-01-29 00:00:00+00'::timestamp with time zone)"
"        Filter: (shared IS FALSE)"
"        ->  Bitmap Index Scan on blocks_created_idx  (cost=0.00..14786.89 rows=550404 width=0) (actual time=277.407..277.407 rows=706663 loops=1)"
"              Index Cond: (created > '2012-01-29 00:00:00+00'::timestamp with time zone)"
"Total runtime: 2693.107 ms"


U sure the new timing isn't owed to cached data? If I am reading it correctly, from the latest explain you posted the Index Scan shouldn't have made a difference as it is reporting pretty much all rows in the table have created > 'yesterday'.
If the number of rows with created < 'yesterday' isn't significant (~ over 25% with default config) a full scan will be chosen and it will probably be the better choice too.



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux