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/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
