Date: Thu, 10 Apr 2008 00:31:00 +0200
To: "John Beaver" <john.e.beaver@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: large tables and simple "= constant" queries using indexes
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <47FD2DF3.80500@gmail.com>
Content-Transfer-Encoding: Quoted-Printable
Message-ID: <op.t9c7hyd7cigqcu@apollo13.peufeu.com>
In-Reply-To: <47FD2DF3.80500@gmail.com>
User-Agent: Opera Mail/9.24 (Linux)


> Hi, I've started my first project with Postgres (after several years o=
f  =

> using Mysql), and I'm having an odd performance problem that I was  =

> hoping someone might be able to explain the cause of.
>
> ----My query----
>     - select count(*) from gene_prediction_view where gene_ref =3D 523=

>     - takes 26 seconds to execute, and returns 2400 (out of a total of=
  =

> 15 million records in the table)
>  ---My problem---
>     Using a single-column index to count 2400 records which are exactl=
y  =

> one constant value doesn't sound like something that would take 26  =

> seconds. What's the slowdown? Any silver bullets that might fix this?

	* Please post an EXPLAIN ANALYZE of your query which will allow to choo=
se  =

between these two options :
	- If Postgres uses a bad plan (like a seq scan), you need to up the  =

statistics for this column
	- If you get the correct plan (index scan or bitmap index scan) then it=
  =

is likely that postgres does one disk seek per row that has to be counte=
d.  =

26 seconds for 2400 rows would be consistent with a 10ms seek time. The =
 =

unmistakable sign is that re-running the query will result in a very fas=
t  =

runtime (I'd say a couple ms for counting 2400 rows if no disk IO is  =

involved).