Re: Poor select count(*) performance

On Mon, Feb 23, 2009 at 05:44:05PM -0800, Mike Ivanov wrote:
> An hour ago it took 8 seconds, one minute ago the same query took just only
> 7 milliseconds.
>
> Any ideas why the execution time varies so wildly?
>
> Explain Analyze gives:
>
> Aggregate (cost=2000.08..2000.09 rows=1 width=0) (actual time=6.962..6.963 rows=1 loops=1)
> -> Index Scan using lingq_card_context_id on lingq_card
> (cost=0.00..1998.68 rows=561 width=0) (actual time=0.025..5.045 rows=2830 loops=1)
> Index Cond: (context_id = 68672)
> Total runtime: 7.011 ms

If you're unlucky in the example above, none of those 2830 rows will be
in memory and you'll have to wait for the disk to bring them all back.
Depending on where these are on disk and how fast your disks are this
could take up to 30 seconds.

If you want this sort of thing to go quicker you could try CLUSTERing
the table on this index, but then this will slow down other queries that
want data to come off the disk in a specific order. It's a balancing
act!

--
Sam http://samason.me.uk/