DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.au;
	h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=BzFb+d0xba9am57lPTyZH0qh8b/mcgKJWAtGy2F7+pAXuND8aUDN6lK8qWsUM9i2ksXNdX20YNMLFM8KawiGYcZrJSv1yiiS2aQDOgXFWc2XFekbkQB0sx6ECt9+t5VQVZpO0mGoTOipj7gq5cede430EU3Po7u8v9tbMTkCaf8=
	;
Message-ID: <20060406023544.37250.qmail@web52305.mail.yahoo.com>
Date: Thu, 6 Apr 2006 12:35:44 +1000 (EST)
From: Brian Herlihy <btherl@yahoo.com.au>
Subject: Query planner is using wrong index.
To: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Hi,

I have a problem with the choice of index made by the query planner.

My table looks like this:

CREATE TABLE t
(
  p1 varchar not null,
  p2 varchar not null,
  p3 varchar not null,
  i1 integer,
  i2 integer,
  i3 integer,
  i4 integer,
  i5 integer,
  d1 date,
  d2 date,
  d3 date,
  PRIMARY KEY (p1, p2, p3)
);

I have also created an index on (p2, p3), as some of my lookups are on these
only.
All the integers and dates are data values.
The table has around 9 million rows.
I am using postgresl 7.4.7

I have set statistics to 1000 on the p1, p2 and p3 columns, and run vacuum full
analyse.  However, I still see
query plans like this:

db=# explain select * from t where p1 = 'something' and p2 = 'fairly_common'
and p3 = 'fairly_common';                                                      
        QUERY PLAN          
-----------------------------------------------------------------------------------------------------------------------------------
 Index Scan using p2p3 on t  (cost=0.00..6.01 rows=1 width=102)
   Index Cond: (((p2)::text = 'fairly_common'::text) AND ((p3)::text =
'fairly_common'::text))
   Filter: ((p1)::text = 'something'::text)
(3 rows)

The problem appears to be this:

db=# explain select * from t where p2 = 'fairly_common' and p3 =
'fairly_common';                                                              
QUERY PLAN          
-----------------------------------------------------------------------------------------------------------------------------------
 Index Scan using p2p3 on t  (cost=0.00..6.01 rows=1 width=102)
   Index Cond: (((p2)::text = 'fairly_common'::text) AND ((p3)::text =
'fairly_common'::text))
(3 rows)

The query planner thinks that this will return only 1 row.
In fact, these index lookups sometimes return up to 500 rows, which then must
be filtered by p1.
This can take 2 or 3 seconds to execute for what should be a simple primary key
lookup.

For VERY common values of p2 and p3, the query planner chooses the primary key,
because these values are stored
explicitly in the analyse results.  For rare values there is no problem,
because the query runs quickly.
But for "fairly common" values, there is a problem.

I would like the query planner to use the primary key for all of these lookups.
 How can I enforce this?

Thanks,
Brian