Re: Why should such a simple query over indexed columns be so slow?
| От | Alessandro Gagliardi |
|---|---|
| Тема | Re: Why should such a simple query over indexed columns be so slow? |
| Дата | |
| Msg-id | CAAB3BBKtjv33Q+oUnPCLSpx1yPagqm9dMowKL4qa6n5uR-iQKg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Why should such a simple query over indexed columns be so slow? (Fernando Hevia <fhevia@gmail.com>) |
| Список | pgsql-performance |
Pretty sure. I just ran the same query twice in a row with enable_seqscan=true and the "actual time" was on the order of 42 seconds both times. With enable_seqscan=false, it was on the order 3 seconds. Going back to enable_seqscan=true, it's back to 42 seconds. Unless you're saying that enable_seqscan is determining whether or not the data is being cached....
On Mon, Jan 30, 2012 at 1:13 PM, Fernando Hevia <fhevia@gmail.com> wrote:
On Mon, Jan 30, 2012 at 17:35, Alessandro Gagliardi <alessandro@path.com> 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.
В списке pgsql-performance по дате отправления: