Skip site navigation (1) Skip section navigation (2)

Re: Bug? 8.0 does not use partial index

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Palle Girgensohn <girgen(at)pingpong(dot)net>
Cc: John Hansen <john(at)geeknet(dot)com(dot)au>, pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: Bug? 8.0 does not use partial index
Date: 2005-01-14 00:32:38
Message-ID: 18084.1105662758@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
I wrote:
> I thought of a fairly miserable hack, which relies on the fact that 8.0
> does know how to accumulate statistics on functional indexes:

Never mind, it turns out that doesn't work the way I thought.  It's
actually falling back to a default estimate :-(.  I still think it'd
be a good idea to use stats on partial indexes in future releases,
but right at the moment we aren't doing any such thing.

Here's an even more miserable hack: use a non-partial functional index
over a multicolumn expression as a poor man's way of creating
cross-column stats.  For example, assuming all this_group_id values are
positive:

group=# create function myfunc(int,int) returns int as
group-# 'SELECT CASE WHEN $2 IS NULL THEN $1 ELSE -$1 END' language sql immutable;
group=# create index fooi2 on group_data (myfunc(this_group_id, group_id));
CREATE INDEX
group=# analyze group_data;
ANALYZE
group=# explain analyze select * from group_data where myfunc(this_group_id, group_id)=46;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Index Scan using fooi2 on group_data  (cost=0.00..2948.85 rows=792 width=43) (actual time=0.171..0.198 rows=4 loops=1)
   Index Cond: (CASE WHEN (group_id IS NULL) THEN this_group_id ELSE (- this_group_id) END = 46)
 Total runtime: 0.304 ms
(3 rows)

Dunno if you're desperate enough to try that ... but it does seem to work.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Palle GirgensohnDate: 2005-01-14 00:41:46
Subject: Re: Bug? 8.0 does not use partial index
Previous:From: Mark KirkwoodDate: 2005-01-13 23:58:43
Subject: Re: Slow PL/pgSQL 8.0.RC5 (7.4.6. 3times faster)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group