Hi, I've started my first project with Postgres (after several years of
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 = 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 exactly
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 choose
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 counted.
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 fast
runtime (I'd say a couple ms for counting 2400 rows if no disk IO is
involved).
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
