Qingqing,
On 7/12/06, Qingqing Zhou <[EMAIL PROTECTED]> wrote:
>
How long is that "unacceptably long time"?
30 seconds.
the problem here is that 29247 doesn't look like a big number so I can't see
why your patch solved the problem, unless the qsort_comparetup() function of
the data type eats too many circles or the cpu is too slow.
Well...when exhibiting the problem, execution stays inside qsort() for
the entire 'delay period' - I don't think its off in some other recess
which is also lacking in interrupt flag checking.
As for the CPU - the machine is a 4 way Opteron, and otherwise
performs well. It does seem odd that the in-memory sort takes as long
as it does - the plan may suggest a reason.
And - the patched version doesn't exhibit the problem.
I just did a
test to invoke a qsort on an "integer" field of a table with 5 million rows,
and sent a SIGINT, the delay is 7 or 8 seconds. I suspect there are some
other places doesn't check interrupts -- what's your query plan?
Plan attached.
Thanks,
Charles Duffy
db1=# explain analyze SELECT DISTINCT ON (NG.vl1, creat, NG.flati) NG.record,
NG.commr1, NG.docst, NG.docin, NG.doc, NG.flato, NG.flati AS flati, NG.creat AS
creat, reportxm.id, reportxm.hide, reportxm.read, reportxm.cc,
reportxm.pending, reportxm.comment, reportxm.envr1, reportxm.time AS time FROM
record_view AS ft, reportxm, record_view AS ft0 WHERE
reportxm.regcompany=261333178 AND reportxm.hide=FALSE AND
reportxm.envr1=NG.envr1 AND ft0.flati=NG.flati AND ((FALSE) OR ( ft0.vl0 IN
(SELECT subst0.id FROM mlist1a AS subst0 WHERE (((subst0.lastname ILIKE
'%monthly%')) OR ((subst0.surname ILIKE '%monthly%')) OR ((subst0.company ILIKE
'%monthly%')) OR ((subst0.company_short ILIKE '%monthly%')))) AND ft0.vl0 IN
(SELECT subst0.id FROM mlist1a AS subst0 WHERE (((subst0.lastname ILIKE
'%report%')) OR ((subst0.surname ILIKE '%report%')) OR ((subst0.company ILIKE
'%report%')) OR ((subst0.company_short ILIKE '%report%'))))) OR (((ft0.vl2
ILIKE '%monthly%' AND ft0.vl2 ILIKE '%report%')))) AND NG.docst=1 ORDER BY
NG.vl1 DESC, creat DESC LIMIT 1000 OFFSET 0;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=133779.07..135568.96 rows=71 width=171) (actual
time=80224.317..80224.744 rows=19 loops=1)
-> Unique (cost=133779.07..135568.96 rows=71 width=171) (actual
time=80224.307..80224.676 rows=19 loops=1)
-> Sort (cost=133779.07..134226.54 rows=178989 width=171) (actual
time=80224.300..80224.430 rows=91 loops=1)
Sort Key: NG.vl1, NG.creat, NG.flati
-> Hash Join (cost=4938.63..118162.72 rows=178989 width=171)
(actual time=79708.318..80222.796 rows=91 loops=1)
Hash Cond: (("outer".envr1)::oid = "inner".envr1)
-> Bitmap Heap Scan on reportxm (cost=530.88..110615.56
rows=28411 width=51) (actual time=581.525..1086.185 rows=105001 loops=1)
Recheck Cond: (regcompany = 261333178)
Filter: (NOT hide)
-> Bitmap Index Scan on reportxm_reg_index
(cost=0.00..530.88 rows=56822 width=0) (actual time=103.483..103.483
rows=176807 loops=1)
Index Cond: (regcompany = 261333178)
-> Hash (cost=4404.60..4404.60 rows=1260 width=124)
(actual time=78972.309..78972.309 rows=20 loops=1) ->
Hash Join (cost=3344.68..4404.60 rows=1260 width=124) (actual
time=78765.710..78972.200 rows=20 loops=1)
Hash Cond: ("outer".flati = "inner".flati)
-> Subquery Scan ft0 (cost=2936.65..3966.22
rows=3550 width=32) (actual time=78211.051..78417.354 rows=20 loops=1)
Filter: (((hashed subplan) AND (hashed
subplan)) OR ((vl2 ~~* '%monthly%'::text) AND (vl2 ~~* '%report%'::text)))
-> Unique (cost=2909.44..3654.99
rows=14201 width=320) (actual time=78196.706..78364.502 rows=29247 loops=1)
-> Sort (cost=2909.44..2944.94
rows=14201 width=320) (actual time=78196.698..78239.799 rows=29247 loops=1)
Sort Key: record, commr1,
envr1, docin, creat, flati, flato, doc, docst, vlord, vl0, vl1, vl2, vl3, vl4,
vl5, vl6, vl7, vl8, vl9
-> Append
(cost=0.00..1930.02 rows=14201 width=320) (actual time=0.052..396.577
rows=29247 loops=1)
-> Subquery Scan
"*SELECT* 1" (cost=0.00..872.30 rows=3965 width=320) (actual
time=0.047..164.191 rows=16382 loops=1)
-> Seq Scan on
recordspecA (cost=0.00..832.65 rows=3965 width=320) (actual time=0.030..87.355
rows=16382 loops=1)
-> Subquery Scan
"*SELECT* 2" (cost=0.00..276.52 rows=2676 width=288) (actual
time=0.042..22.825 rows=2256 loops=1)
-> Seq Scan on
recordspecB (cost=0.00..249.76 rows=2676 width=288) (actual time=0.022..12.342
rows=2256 loops=1)
-> Subquery Scan
"*SELECT* 3" (cost=0.00..456.32 rows=4416 width=320) (actual
time=0.041..62.629 rows=6355 loops=1)
-> Seq Scan on
recordspecC (cost=0.00..412.16 rows=4416 width=320) (actual time=0.022..33.343
rows=6355 loops=1)
-> Subquery Scan
"*SELECT* 4" (cost=0.00..324.88 rows=3144 width=288) (actual
time=0.037..41.384 rows=4254 loops=1)
-> Seq Scan on
recordspecC (cost=0.00..293.44 rows=3144 width=288) (actual time=0.020..21.561
rows=4254 loops=1)
SubPlan
-> Seq Scan on mlist1a subst0
(cost=0.00..13.60 rows=1 width=4) (never executed)
Filter: ((lastname ~~*
'%report%'::text) OR (surname ~~* '%report%'::text) OR
(company ~~* '%report%'::text) OR (company_short ~~* '%report%'::text))
-> Seq Scan on mlist1a subst0
(cost=0.00..13.60 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)
Filter: ((lastname ~~*
'%monthly%'::text) OR (surname ~~* '%monthly%'::text) OR (company ~~*
'%monthly%'::text) OR (company_short ~~* '%monthly%'::text))
-> Hash (cost=407.85..407.85 rows=71
width=124) (actual time=554.536..554.536 rows=10932 loops=1)
-> Subquery Scan ft
(cost=403.42..407.85 rows=71 width=124) (actual time=318.749..496.203
rows=10932 loops=1)
-> Unique (cost=403.42..407.14
rows=71 width=320) (actual time=318.738..448.646 rows=10932 loops=1)
-> Sort
(cost=403.42..403.59 rows=71 width=320) (actual time=318.727..339.305
rows=10932 loops=1)
Sort Key: record,
commr1, envr1, docin, creat, flati, flato, doc, docst, vlord, vl0, vl1, vl2,
vl3, vl4, vl5, vl6, vl7, vl8, vl9
-> Append
(cost=78.88..401.23 rows=71 width=320) (actual time=5.197..192.868 rows=10932
loops=1)
-> Subquery
Scan "*SELECT* 1" (cost=78.88..148.98 rows=20 width=320) (actual
time=5.192..77.696 rows=5494 loops=1)
-> Bitmap
Heap Scan on recordspecA (cost=78.88..148.78 rows=20 width=320) (actual
time=5.172..51.791 rows=5494 loops=1)
Recheck Cond: (docst = 1)
->
Bitmap Index Scan on recordspecA_envr1 (cost=0.00..78.88 rows=20 width=0)
(actual time=4.787..4.787 rows=5494 loops=1)
Index Cond: (docst = 1)
-> Subquery
Scan "*SELECT* 2" (cost=20.37..62.17 rows=13 width=288) (actual
time=1.067..17.539 rows=1223 loops=1)
-> Bitmap
Heap Scan on recordspecB (cost=20.37..62.04 rows=13 width=288) (actual
time=1.049..11.626 rows=1223 loops=1)
Recheck Cond: (docst = 1)
->
Bitmap Index Scan on recordspecB_envr1 (cost=0.00..20.37 rows=13 width=0)
(actual time=0.899..0.899 rows=1223 loops=1)
Index Cond: (docst = 1)
-> Subquery
Scan "*SELECT* 3" (cost=40.46..110.96 rows=22 width=320) (actual
time=2.252..35.328 rows=2540 loops=1)
-> Bitmap
Heap Scan on recordspecC (cost=40.46..110.74 rows=22 width=320) (actual
time=2.236..23.051 rows=2540 loops=1)
Recheck Cond: (docst = 1)
->
Bitmap Index Scan on recordspecC_envr1 (cost=0.00..40.46 rows=22 width=0)
(actual time=2.020..2.020 rows=2540 loops=1)
Index Cond: (docst = 1)
-> Subquery
Scan "*SELECT* 4" (cost=28.00..79.13 rows=16 width=288) (actual
time=1.496..22.882 rows=1675 loops=1)
-> Bitmap
Heap Scan on recordspecC (cost=28.00..78.97 rows=16 width=288) (actual
time=1.478..14.949 rows=1675 loops=1)
Recheck Cond: (docst = 1)
->
Bitmap Index Scan on recordspecC_envr1 (cost=0.00..28.00 rows=16 width=0)
(actual time=1.336..1.336 rows=1675 loops=1)
Index Cond: (docst = 1)
Total runtime: 80239.122 ms
(60 rows)
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
