DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject:content-type:content-transfer-encoding;
	b=bPKlwYyo/Hw6pCWLZart3AdksotPml4rAZLKq8EnV9iE64q0yuGacLcYkgd+NOayfO/DvWAwV3MZxYa+/pWINpYeQE7PcesTs3kFqcibpKVXNyHPajxMU8pVdVZbOus1/2+X+3HmDyFAyRAFzz4Qk25yxSuQvj9ylHWECESJJFM=
Message-ID: <47FD2DF3.80500@gmail.com>
Date: Wed, 09 Apr 2008 16:58:27 -0400
From: John Beaver <john.e.beaver@gmail.com>
User-Agent: Thunderbird 2.0.0.12 (Macintosh/20080213)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: large tables and simple "= constant" queries using indexes
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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?

----Steps I've taken----
    - I ran vacuum and analyze
    - I upped the shared_buffers to 58384, and I upped some of the other 
postgresql.conf values as well. Nothing seemed to help significantly, 
but maybe I missed something that would help specifically for this query 
type?
    - I tried to create a hash index, but gave up after more than 4 
hours of waiting for it to finish indexing

----Table stats----
    - 15 million rows; I'm expecting to have four or five times this 
number eventually.
    - 1.5 gigs of hard drive usage

----My development environment---
    - 2.6ghz dual-core MacBook Pro with 4 gigs of ram and a 7200 rpm 
hard drive
    - OS X 10.5.2
    - Postgres 8.3 (installed via MacPorts)

----My table----

CREATE TABLE gene_prediction_view
(
  id serial NOT NULL,
  gene_ref integer NOT NULL,
  go_id integer NOT NULL,
  go_description character varying(200) NOT NULL,
  go_category character varying(50) NOT NULL,
  function_verified_exactly boolean NOT NULL,
  function_verified_with_parent_go boolean NOT NULL,
  function_verified_with_child_go boolean NOT NULL,
  score numeric(10,2) NOT NULL,
  precision_score numeric(10,2) NOT NULL,
  CONSTRAINT gene_prediction_view_pkey PRIMARY KEY (id),
  CONSTRAINT gene_prediction_view_gene_ref_fkey FOREIGN KEY (gene_ref)
      REFERENCES sgd_annotations (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT gene_prediction_view_go_id_fkey FOREIGN KEY (go_id)
      REFERENCES go_terms (term) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT gene_prediction_view_gene_ref_key UNIQUE (gene_ref, go_id)
)
WITH (OIDS=FALSE);
ALTER TABLE gene_prediction_view OWNER TO postgres;

CREATE INDEX ix_gene_prediction_view_gene_ref
  ON gene_prediction_view
  USING btree
  (gene_ref);