Re: [PERFORM] two seperate queries run faster than queries ORed
Stephan Szabo <[EMAIL PROTECTED]> writes: > Well, you have to be careful on the combination to not give the wrong > answers if there's a row with u.status=3 that matches a row d.status=3. We could in theory handle that using something similar to the method currently used for "OR" indexscans (that is, rather than doing either UNION- or UNION-ALL-like processing, we drop tuples from later scans that meet the qual tests of the earlier scans). However I don't see any clean way in the current planner to cost out both approaches and pick the cheaper one. It looks to me like we'd have to do over the *entire* join planning process each way, which is ugly as well as unreasonably expensive. The problem is that the OR approach only wins when the component clauses of the OR can drop down to lower levels of the plan tree if they are considered separately. But a plan tree with a restriction at a low level and one without it are two different things, and the dynamic-programming approach we use to build up join plans doesn't yield the same solutions. (As indeed it shouldn't, since the whole point of Joseph's example is to get fundamentally different plans for the two parts of the OR.) We could possibly approach it heuristically, that is examine the clauses and try to guess whether it's better to split them apart or not. But even assuming that we punt on that part of the problem, it seems like a mess. For instance suppose that there are additional relations in the query that aren't mentioned in the OR clause. The planner may want to join some of those relations in advance of forming the join that the OR itself describes. Pushing down different parts of the OR might cause the best join path to change. How could you merge multiple scans if some include extra relations and some don't? In short, I see how such a plan could be executed, but I don't see any effective approach for generating the plan ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] two seperate queries run faster than queries ORed together
Stephan Szabo wrote: On Mon, 22 Mar 2004, Joseph Shraibman wrote: Tom Lane wrote: Joseph Shraibman <[EMAIL PROTECTED]> writes: 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. ... and every one of those rows *must* be included in the join input, *If* you use one big join in the first place. If postgres ran the query to first get the values with status == 3 from u, then ran the query to get the entries from d, then combined them, the result would be the same but the output faster. Instead it is doing seq scans on both tables and Well, you have to be careful on the combination to not give the wrong answers if there's a row with u.status=3 that matches a row d.status=3. Right you would have to avoid duplicates. The existing DISTINCT code should be able to handle that. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] two seperate queries run faster than queries ORed
On Mon, 22 Mar 2004, Joseph Shraibman wrote: > Tom Lane wrote: > > Joseph Shraibman <[EMAIL PROTECTED]> writes: > > > >>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. > > > > > > ... and every one of those rows *must* be included in the join input, > > *If* you use one big join in the first place. If postgres ran the query > to first get the values with status == 3 from u, then ran the query to > get the entries from d, then combined them, the result would be the same > but the output faster. Instead it is doing seq scans on both tables and Well, you have to be careful on the combination to not give the wrong answers if there's a row with u.status=3 that matches a row d.status=3. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] two seperate queries run faster than queries ORed together
Tom Lane wrote: Joseph Shraibman <[EMAIL PROTECTED]> writes: 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. ... and every one of those rows *must* be included in the join input, *If* you use one big join in the first place. If postgres ran the query to first get the values with status == 3 from u, then ran the query to get the entries from d, then combined them, the result would be the same but the output faster. Instead it is doing seq scans on both tables and doing an expensive join that returns only a few rows. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] two seperate queries run faster than queries ORed together
Joseph Shraibman <[EMAIL PROTECTED]> writes: > 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. ... and every one of those rows *must* be included in the join input, regardless of its status value, because it might join to some d row that has status=3. Conversely, every single row of d must be considered in the join because it might join to some u row with status=3. So any way you slice it, this query requires a large and expensive join operation, no matter that there are only a few rows with the right status values in the other table. I'd rewrite the query if I were you. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] two seperate queries run faster than queries ORed together
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.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] two seperate queries run faster than queries ORed together
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.
I'd start by analyze-ing the table in question, and if that doesn't have any
effect look at the column stats and see what spread of values it thinks you
have.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
