Re: large tables and simple "= constant" queries using indexes

> Hi, I've started my first project with Postgres (after several years of=
=20=20
> using Mysql), and I'm having an odd performance problem that I was=20=20
> 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=20=
=20
> 15 million records in the table)
>  ---My problem---
>     Using a single-column index to count 2400 records which are exactly=
=20=20
> one constant value doesn't sound like something that would take 26=20=20
> seconds. What's the slowdown? Any silver bullets that might fix this?

	* Please post an EXPLAIN ANALYZE of your query which will allow to
choose=
=20=20
between these two options :
	- If Postgres uses a bad plan (like a seq scan), you need to up the=20=20
statistics for this column
	- If you get the correct plan (index scan or bitmap index scan) then
it=20=
=20
is likely that postgres does one disk seek per row that has to be
counted.=
=20=20
26 seconds for 2400 rows would be consistent with a 10ms seek time.
The=20=
=20
unmistakable sign is that re-running the query will result in a very fast=
=20=20
runtime (I'd say a couple ms for counting 2400 rows if no disk IO is=20=20
involved).


--=20
Sent via pgsql-performance mailing list (pgsql-performance@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance