Subject: Re: Query planner is using wrong index.
From: Ragnar <gnari@hive.is>
To: Brian Herlihy <btherl@yahoo.com.au>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <20060406023544.37250.qmail@web52305.mail.yahoo.com>
References: <20060406023544.37250.qmail@web52305.mail.yahoo.com>
Content-Type: text/plain
Date: Thu, 06 Apr 2006 08:52:54 +0000
Message-Id: <1144313574.32289.78.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On fim, 2006-04-06 at 12:35 +1000, Brian Herlihy wrote:

> 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 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)

please show us an actual EXPLAIN ANALYZE
this will show us more.

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

How would that help? have you tested to see if it would 
actualy be better?

gnari