You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@manifoldcf.apache.org by "Karl Wright (JIRA)" <ji...@apache.org> on 2013/04/24 00:03:16 UTC
[jira] [Commented] (CONNECTORS-678) Postgresql generating terrible
plans for stuffer queries
[ https://issues.apache.org/jira/browse/CONNECTORS-678?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13639690#comment-13639690 ]
Karl Wright commented on CONNECTORS-678:
----------------------------------------
While this was reported on 9.1.9, I was able to reproduce it on my postgresql version also (8.4).
The following query:
{code}
EXPLAIN SELECT t0.id,t0.jobid,t0.dochash,t0.docid,t0.status,t0.failtime,t0.failcount,t0.priorityset FROM jobqueue t0 WHERE
t0.status IN ('P','G') AND t0.checkaction='R' AND t0.checktime<=1466628406182 AND
EXISTS(SELECT 'x' FROM jobs t1 WHERE t1.status IN ('A','a') AND t1.id=t0.jobid AND t1.priority=5)
ORDER BY t0.docpriority ASC,t0.status ASC,t0.checkaction ASC,t0.checktime ASC LIMIT 100;
{code}
... gives this plan, which is fine:
{code}
Limit (cost=11.20..491.36 rows=100 width=181)
-> Nested Loop Semi Join (cost=11.20..4092.51 rows=850 width=181)
Join Filter: (t0.jobid = t1.id)
-> Index Scan using i1333676554296 on jobqueue t0 (cost=0.00..4062.19 rows=850 width=181)
Index Cond: ((checkaction = 'R'::bpchar) AND (checktime <= 1466628406182::bigint))
Filter: (status = ANY ('{P,G}'::bpchar[]))
-> Materialize (cost=11.20..11.21 rows=1 width=8)
-> Seq Scan on jobs t1 (cost=0.00..11.20 rows=1 width=8)
Filter: ((status = ANY ('{A,a}'::bpchar[])) AND (priority = 5))
(9 rows)
{code}
But, adding in EITHER of the NOT EXISTS clauses, even the prereqevents one (which has zero rows), and you get this:
{code}
Limit (cost=934.12..934.37 rows=100 width=171)
-> Sort (cost=934.12..935.90 rows=710 width=171)
Sort Key: t0.docpriority
-> Nested Loop (cost=90.77..906.99 rows=710 width=171)
Join Filter: (t0.jobid = t1.id)
-> HashAggregate (cost=11.20..11.21 rows=1 width=8)
-> Seq Scan on jobs t1 (cost=0.00..11.20 rows=1 width=8)
Filter: ((status = ANY ('{A,a}'::bpchar[])) AND (priority = 5))
-> Hash Anti Join (cost=79.57..886.90 rows=710 width=171)
Hash Cond: (t0.id = t3.owner)
-> Bitmap Heap Scan on jobqueue t0 (cost=51.34..849.19 rows=850 width=171)
Recheck Cond: ((status = ANY ('{P,G}'::bpchar[])) AND (checkaction = 'R'::bpchar) AND (checktime <= 1566628406182::bigint))
-> Bitmap Index Scan on i1333676554298 (cost=0.00..51.13 rows=850 width=0)
Index Cond: ((status = ANY ('{P,G}'::bpchar[])) AND (checkaction = 'R'::bpchar) AND (checktime <= 1566628406182::bigint))
-> Hash (cost=26.48..26.48 rows=140 width=8)
-> Hash Join (cost=13.15..26.48 rows=140 width=8)
Hash Cond: ((t3.eventname)::text = (t4.name)::text)
-> Seq Scan on prereqevents t3 (cost=0.00..11.40 rows=140 width=524)
-> Hash (cost=11.40..11.40 rows=140 width=516)
-> Seq Scan on events t4 (cost=0.00..11.40 rows=140 width=516)
(20 rows)
{code}
... which is a clearly bad plan.
I'm not sure exactly when PostgreSQL lost the ability to properly execute this query, but it is clearly broken right at the moment.
> Postgresql generating terrible plans for stuffer queries
> --------------------------------------------------------
>
> Key: CONNECTORS-678
> URL: https://issues.apache.org/jira/browse/CONNECTORS-678
> Project: ManifoldCF
> Issue Type: Bug
> Components: Framework core
> Affects Versions: ManifoldCF 1.1.1
> Reporter: Karl Wright
> Assignee: Karl Wright
> Fix For: ManifoldCF 1.2
>
>
> Query plans like this seem to be taking place:
> {code}
> WARN 2013-04-22 13:01:20,414 (Stuffer thread) - Plan: Limit (cost=9597.49..9597.49 rows=1 width=155)
> WARN 2013-04-22 13:01:20,414 (Stuffer thread) - Plan: -> Sort (cost=9597.49..9597.49 rows=1 width=155)
> WARN 2013-04-22 13:01:20,414 (Stuffer thread) - Plan: Sort Key: t0.docpriority
> WARN 2013-04-22 13:01:20,414 (Stuffer thread) - Plan: -> Nested Loop Anti Join (cost=17.68..9597.48 rows=1 width=155)
> WARN 2013-04-22 13:01:20,414 (Stuffer thread) - Plan: Join Filter: (t2.jobid <> t0.jobid)
> WARN 2013-04-22 13:01:20,414 (Stuffer thread) - Plan: -> Nested Loop Semi Join (cost=17.68..9588.87 rows=1 width=155)
> WARN 2013-04-22 13:01:20,414 (Stuffer thread) - Plan: Join Filter: (t0.jobid = t1.id)
> WARN 2013-04-22 13:01:20,414 (Stuffer thread) - Plan: -> Nested Loop Anti Join (cost=17.68..9586.81 rows=1 width=155)
> WARN 2013-04-22 13:01:20,414 (Stuffer thread) - Plan: Join Filter: (t0.id = t3.owner)
> WARN 2013-04-22 13:01:20,414 (Stuffer thread) - Plan: -> Bitmap Heap Scan on jobqueue t0 (cost=4.53..6.54 rows=1 width=155)
> WARN 2013-04-22 13:01:20,414 (Stuffer thread) - Plan: Recheck Cond: (status = ANY ('{P,G}'::bpchar[]))
> WARN 2013-04-22 13:01:20,414 (Stuffer thread) - Plan: Filter: ((checktime <= 1366628406182::bigint) AND (checkaction = 'R'::bpchar))
> WARN 2013-04-22 13:01:20,414 (Stuffer thread) - Plan: -> Bitmap Index Scan on i1362584563122 (cost=0.00..4.53 rows=1 width=0)
> WARN 2013-04-22 13:01:20,414 (Stuffer thread) - Plan: Index Cond: (status = ANY ('{P,G}'::bpchar[]))
> WARN 2013-04-22 13:01:20,414 (Stuffer thread) - Plan: -> Hash Join (cost=13.15..7635.56 rows=231554 width=8)
> WARN 2013-04-22 13:01:20,414 (Stuffer thread) - Plan: Hash Cond: ((t3.eventname)::text = (t4.name)::text)
> WARN 2013-04-22 13:01:20,414 (Stuffer thread) - Plan: -> Seq Scan on prereqevents t3 (cost=0.00..4438.54 rows=231554 width=37)
> WARN 2013-04-22 13:01:20,414 (Stuffer thread) - Plan: -> Hash (cost=11.40..11.40 rows=140 width=516)
> WARN 2013-04-22 13:01:20,414 (Stuffer thread) - Plan: -> Seq Scan on events t4 (cost=0.00..11.40 rows=140 width=516)
> WARN 2013-04-22 13:01:20,414 (Stuffer thread) - Plan: -> Seq Scan on jobs t1 (cost=0.00..2.03 rows=2 width=8)
> WARN 2013-04-22 13:01:20,414 (Stuffer thread) - Plan: Filter: ((status = ANY ('{A,a}'::bpchar[])) AND (priority = 5::bigint))
> WARN 2013-04-22 13:01:20,414 (Stuffer thread) - Plan: -> Index Scan using i1362584563120 on jobqueue t2 (cost=0.00..4.30 rows=1 width=49)
> WARN 2013-04-22 13:01:20,414 (Stuffer thread) - Plan: Index Cond: ((dochash)::text = (t0.dochash)::text)
> WARN 2013-04-22 13:01:20,414 (Stuffer thread) - Plan: Filter: (status = ANY ('{A,F,a,f,D,d}'::bpchar[]))
> WARN 2013-04-22 13:01:20,414 (Stuffer thread) -
> {code}
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira