Poor select count(*) performance

From: Mike Ivanov <mike(at)thelinguist(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Poor select count(*) performance
Date: 2009-02-24 01:44:05
Message-ID: d55c18a50902231744j3425402en58fcf4d2ad9c4bde@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi there,

I'm sorry for a stupid question but I'm really stuck.

A query:

SELECT COUNT(*) FROM "lingq_card" WHERE "lingq_card"."context_id" = ...;

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

The lingq_cards table contains about 1.4 million rows.

Thanks,
Mike

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2009-02-24 01:56:00 Re: Poor select count(*) performance
Previous Message Jordan Tomkinson 2009-02-24 00:17:54 Re: High cpu usage after many inserts