From: Joseph Shraibman <jks@selectacast.net>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: two seperate queries run faster than queries ORed together
Date: Mon, 22 Mar 2004 12:55:31 -0500
Message-ID: <405F2893.5090805@selectacast.net> (raw)
In-Reply-To: <200403221655.28667.dev@archonet.com>
References: <c3d3sm$fdg$1@news.hub.org>
<200403221655.28667.dev@archonet.com>
Richard Huxton wrote:
> On Thursday 18 March 2004 21:21, Joseph Shraibman wrote:
>
>>explain
>>SELECT COUNT(u.ukey) FROM u, d WHERE d.ukey = u.ukey AND u.pkey = 260
>>AND (u.status = 3 OR d.status = 3 ) AND NOT u.boolfield ;
>>
>>
>> QUERY PLAN
>>---------------------------------------------------------------------------
>>------------ Aggregate (cost=128867.45..128867.45 rows=1 width=4)
>> -> Hash Join (cost=32301.47..128866.77 rows=272 width=4)
>> Hash Cond: ("outer".ukey = "inner".ukey)
>> Join Filter: (("inner".status = 3) OR ("outer".status = 3))
>> -> Seq Scan on u (cost=0.00..41215.97 rows=407824 width=6)
>> Filter: ((pkey = 260) AND (NOT boolfield))
>
>
> There's your problem. For some reason it thinks it's getting 407,824 rows back
> from that filtered seq-scan. I take it that pkey is a primary-key and is
> defined as being UNIQUE? If you actually did have several hundred thousand
> matches then a seq-scan might be sensible.
>
No, pkey is not the primary key in this case. The number of entries in u
that have pkey 260 and not boolfield is 344706. The number of those that
have status == 3 is 7. To total number of entries in d that have status
== 3 is 4.
> I'd start by analyze-ing the table in question,
Is done every night.
The problem is that it seems the planner doesn't think to do the
different parts of the OR seperately and then combine the answers.
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: jks@selectacast.net, dev@archonet.com
Subject: Re: two seperate queries run faster than queries ORed together
In-Reply-To: <405F2893.5090805@selectacast.net>
* 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