From: PFC <lists@peufeu.com>
To: John Beaver <john.e.beaver@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: large tables and simple "= constant" queries using indexes
Date: Thu, 10 Apr 2008 00:31:00 +0200
Message-ID: <op.t9c7hyd7cigqcu@apollo13.peufeu.com> (raw)
In-Reply-To: <47FD2DF3.80500@gmail.com>
References: <47FD2DF3.80500@gmail.com>
> 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).
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, john.e.beaver@gmail.com
Subject: Re: large tables and simple "= constant" queries using indexes
In-Reply-To: <op.t9c7hyd7cigqcu@apollo13.peufeu.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox