From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Anne Rosset <arosset@collab.net>
To: tv@fuzzy.cz
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query postgres 8.3
Date: Mon, 11 Apr 2011 11:59:43 -0500
Message-ID: <4DA2ED2F020000250003C657@gw.wicourts.gov> (raw)
In-Reply-To: <945629628BB0174D86709AFE6D1CDEF5017A66E3@SP-EXCHMBC.sp.corp.collab.net>
References: <945629628BB0174D86709AFE6D1CDEF5017A66C1@SP-EXCHMBC.sp.corp.collab.net>
<50d8f2eb94b393c11221c9d5232d7326.squirrel@sq.gransy.com>
<945629628BB0174D86709AFE6D1CDEF5017A66E3@SP-EXCHMBC.sp.corp.collab.net>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, arosset@collab.net, tv@fuzzy.cz
Subject: Re: Slow query postgres 8.3
In-Reply-To: <4DA2ED2F020000250003C657@gw.wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox