From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rusty Conover <rconover@infogears.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query Plan - Bitmap Index Scan and Views
Date: Fri, 04 Aug 2006 22:15:18 -0400
Message-ID: <24233.1154744118@sss.pgh.pa.us> (raw)
In-Reply-To: <26497C9E-3583-4EA2-A3AD-955F31E00CC3@infogears.com>
References: <26497C9E-3583-4EA2-A3AD-955F31E00CC3@infogears.com>
Rusty Conover <rconover@infogears.com> writes:
> Is there any inherent benefit of using a the IN operator versus
> joining a temporary table? Should they offer near equal performance?
> It appears bitmap scan's aren't done when matching across a small
> temporary table.
I believe the problem you're facing is that existing PG releases
don't know how to rearrange join order in the face of outer joins,
and your view is full of outer joins. So the join against the temp
table happens after forming the full output of the view, whereas you
desperately need it to happen at the bottom of the join stack.
CVS tip (8.2-to-be) has some ability to rearrange outer joins, and
I'm interested to know whether it's smart enough to fix your problem.
But you have not provided enough info to let someone else duplicate
your test case. Would you be willing to download CVS or a recent
nightly snapshot and see what it does with your problem?
regards, tom lane
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: tgl@sss.pgh.pa.us, rconover@infogears.com
Subject: Re: Query Plan - Bitmap Index Scan and Views
In-Reply-To: <24233.1154744118@sss.pgh.pa.us>
* 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