Skip site navigation (1) Skip section navigation (2)

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

From: Alessandro Gagliardi <alessandro(at)path(dot)com>
To: Fernando Hevia <fhevia(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Why should such a simple query over indexed columns be so slow?
Date: 2012-01-30 21:45:35
Message-ID: CAAB3BBKtjv33Q+oUnPCLSpx1yPagqm9dMowKL4qa6n5uR-iQKg@mail.gmail.com (view raw or flat)
Thread:
Lists: 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(at)gmail(dot)com> wrote:
>
> On Mon, Jan 30, 2012 at 17:35, Alessandro Gagliardi <alessandro(at)path(dot)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.
>
>
>

In response to

pgsql-performance by date

Next:From: Alessandro GagliardiDate: 2012-01-30 21:55:00
Subject: Re: Why should such a simple query over indexed columns be so slow?
Previous:From: Scott MarloweDate: 2012-01-30 21:45:34
Subject: Re: Why should such a simple query over indexed columns be so slow?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group