[
https://issues.apache.org/jira/browse/CONNECTORS-678?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13639741#comment-13639741
]
Karl Wright edited comment on CONNECTORS-678 at 4/23/13 10:46 PM:
------------------------------------------------------------------
The indexes for the jobqueue table are:
{code}
The seven indices which are available for the jobqueue table on the client site
is (made visible by using "create scrip" in PGAdmin:
1. CREATE UNIQUE INDEX i1362584563120
ON jobqueue
USING btree
(dochash COLLATE pg_catalog."default", jobid);
2. CREATE INDEX i1362584563121
ON jobqueue
USING btree
(docpriority, status COLLATE pg_catalog."default", checkaction COLLATE
pg_catalog."default", checktime);
3. CREATE INDEX i1362584563122
ON jobqueue
USING btree
(status COLLATE pg_catalog."default", priorityset);
4. CREATE INDEX i1362584563123
ON jobqueue
USING btree
(status COLLATE pg_catalog."default", checkaction COLLATE
pg_catalog."default", checktime);
5. CREATE INDEX i1362584563124
ON jobqueue
USING btree
(failtime, jobid);
6. CREATE INDEX i1362584563125
ON jobqueue
USING btree
(isseed COLLATE pg_catalog."default", jobid);
7. CREATE INDEX i1362584563126
ON jobqueue
USING btree
(jobid, status COLLATE pg_catalog."default");
{code}
The same indexes on my test site are:
{code}
Indexes:
"jobqueue_pkey" PRIMARY KEY, btree (id)
"i1333676554295" UNIQUE, btree (dochash, jobid)
"i1333676554296" btree (docpriority, status, checkaction, checktime)
"i1333676554297" btree (status, priorityset)
"i1333676554298" btree (status, checkaction, checktime)
"i1333676554299" btree (failtime, jobid)
"i1333676554300" btree (isseed, jobid)
"i1333676554301" btree (jobid, status)
{code}
The indexes for the jobs table (from my instance):
{code}
Indexes:
"jobs_pkey" PRIMARY KEY, btree (id)
"i1333676554303" btree (connectionname)
"i1333676554304" btree (connectionname)
"i1333676554305" btree (status, id, priority)
{code}
For the prereqevents table:
{code}
Indexes:
"i1333676554302" btree (owner)
{code}
For the events table:
{code}
Indexes:
"events_pkey" PRIMARY KEY, btree (name)
{code}
was (Author: [email protected]):
The indexes for the jobqueue table are:
{code}
The seven indices which are available for the jobqueue table is (made visible
by using "create scrip" in PGAdmin:
1. CREATE UNIQUE INDEX i1362584563120
ON jobqueue
USING btree
(dochash COLLATE pg_catalog."default", jobid);
2. CREATE INDEX i1362584563121
ON jobqueue
USING btree
(docpriority, status COLLATE pg_catalog."default", checkaction COLLATE
pg_catalog."default", checktime);
3. CREATE INDEX i1362584563122
ON jobqueue
USING btree
(status COLLATE pg_catalog."default", priorityset);
4. CREATE INDEX i1362584563123
ON jobqueue
USING btree
(status COLLATE pg_catalog."default", checkaction COLLATE
pg_catalog."default", checktime);
5. CREATE INDEX i1362584563124
ON jobqueue
USING btree
(failtime, jobid);
6. CREATE INDEX i1362584563125
ON jobqueue
USING btree
(isseed COLLATE pg_catalog."default", jobid);
7. CREATE INDEX i1362584563126
ON jobqueue
USING btree
(jobid, status COLLATE pg_catalog."default");
{code}
The indexes for the jobs table (from my instance):
{code}
Indexes:
"jobs_pkey" PRIMARY KEY, btree (id)
"i1333676554303" btree (connectionname)
"i1333676554304" btree (connectionname)
"i1333676554305" btree (status, id, priority)
{code}
For the prereqevents table:
{code}
Indexes:
"i1333676554302" btree (owner)
{code}
For the events table:
{code}
Indexes:
"events_pkey" PRIMARY KEY, btree (name)
{code}
> 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
