Message-Id: <4DA2ED2F020000250003C657@gw.wicourts.gov>
Date: Mon, 11 Apr 2011 11:59:43 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Anne Rosset" <arosset@collab.net>,<tv@fuzzy.cz>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: Slow query postgres 8.3
References: 
 <945629628BB0174D86709AFE6D1CDEF5017A66C1@SP-EXCHMBC.sp.corp.collab.net>
	<50d8f2eb94b393c11221c9d5232d7326.squirrel@sq.gransy.com>
	<945629628BB0174D86709AFE6D1CDEF5017A66E3@SP-EXCHMBC.sp.corp.collab.net>
In-Reply-To: 
 <945629628BB0174D86709AFE6D1CDEF5017A66E3@SP-EXCHMBC.sp.corp.collab.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

"Anne Rosset" <arosset@collab.net> wrote:
 
>                            ->  Index Scan using role_oper_obj_oper
> on role_operation  (cost=0.00..93.20 rows=45 width=9) (actual
> time=0.236..71.291 rows=6108 loops=1)
>                                  Index Cond:
> (((object_type_id)::text = 'SfMain.Project'::text) AND
> ((operation_category)::text = 'admin'::text) AND
> ((operation_name)::text = 'admin'::text))
 
This looks like another case where there is a correlation among
multiple values used for selection.  The optimizer assumes, for
example, that category = 'admin' will be true no more often for rows
with operation_name = 'admin' than for other values of
operation_name.  There has been much talk lately about how to make
it smarter about that, but right now there's no general solution,
and workarounds can be tricky.
 
In more recent versions you could probably work around this with a
Common Table Expression (CTE) (using a WITH clause).  In 8.3 the
best idea which comes immediately to mind is to select from the
role_operation table into a temporary table using whichever of those
three criteria is most selective, and then join that temporary table
into the rest of the query.  Maybe someone else can think of
something better.
 
-Kevin