|
|
该查询花费的时间太长。# F Q% b2 B3 A" D! J, i$ G3 H
explain analyze* O$ H% a% M5 L" A4 I% h
select! h: t# R2 }, n7 Y* ~
c.company_rec_id,
+ F, v% A$ g8 ec.the_company_code , B% O8 D2 H+ I" }' `! O0 S( ]& i
c.company
8 A O& f. \5 P" ^& x" Z8 Tfrom
6 o0 z" r% ]# Ntlist t& A- z+ Z& o4 Z4 B* Y
-- it is questionable why this query become fast when using left join, the most natural query is inner join...
. s* g% [! d" z* }) z, Zjoin mlist m using(mlist_rec_id)
n" s3 ?8 F) M9 _join parcel_application ord_app using(parcel_application_rec_id); o' f' f5 }/ j- X V& N4 J
join parcel ord using(parcel_rec_id)
8 |- d) n3 i9 Ljoin company c on c.company_rec_id = ord.client_rec_id, V0 ^. d( G6 @; |9 G3 b: q" }* |' W
-- ...questionable
4 z4 p+ }* |# Vwhere $ [& @5 l; W/ |8 S- {3 o, f3 Y
(
: w0 N' s+ R! H9 u* Y7 l. H: _ 'cadmium' = ''7 S. g9 b% z+ ?2 X! B! z
or
j9 l& D! X, ] T: w7 t$ T exists
' S4 ` H/ m1 ?2 g/ K; `* F+ ~ (6 X$ D* V4 k& F: p
select *
% m% J, K5 P" Y6 G0 c- X# R from mlist_detail md3 C9 A# p' b; _+ c- W0 \% G
where 6 S s* Q8 W5 U r; f
md.mlist_rec_id = m.mlist_rec_id
% T, W# e3 |( e& s0 ]5 \' Q and exists1 _$ d6 N- `; }' L' B( K! e2 L
(4 Q; A& \# `, z: ~" t: G2 D
select *
$ x7 q3 z p6 E+ N! Q4 c% j from mlist_detail_parameter mdp / a( v, r8 l6 f1 g4 D9 b$ Z
join parameter p using(parameter_rec_id)
/ A# P3 p9 v0 z where mdp.mlist_detail_rec_id = md.mlist_detail_rec_id
4 ^ l9 l# ^4 z3 b( N/ S and to_tsvector(extract_words(p.parameter)) @@ plainto_tsquery(extract_words('cadmium')) , Y6 q: `: b4 S9 T, U
)7 j- m/ U4 b! o
)' Z7 d6 x6 Z/ I2 I; `" c# r" F8 w$ S& V
)
( X! M% _: V! S( N2 {group by c.company_rec_id, ) F# x9 L- K1 |+ _5 Q( o
c.the_company_code, c.company
d$ X6 l) \: B+ }+ T/ o…这是执行计划：2 z8 v' P' p @7 @0 `
"HashAggregate (cost=3791153.43..3791156.38 rows=295 width=71) (actual time=222284.340..222284.357 rows=84 loops=1)"9 G7 r# ~1 {; k6 O& r6 V
" -> Nested Loop (cost=0.00..3791139.81 rows=1816 width=71) (actual time=421.412..222282.253 rows=715 loops=1)"
; k! s9 e( ?( ^) g" -> Merge Join (cost=0.00..3790598.03 rows=1816 width=37) (actual time=421.395..222273.876 rows=715 loops=1)": m8 r) [ k' J) `+ x
" Merge Cond: ((ord_app.parcel_rec_id)::text = (ord.parcel_rec_id)::text)"
6 _' V9 i) W/ e% S3 T" -> Nested Loop (cost=0.00..3790229.12 rows=1816 width=37) (actual time=421.371..222264.556 rows=715 loops=1)"2 ~! `* @' s5 N
" -> Nested Loop (cost=0.00..3789062.60 rows=1816 width=74) (actual time=421.346..222250.614 rows=715 loops=1)"
8 x4 s+ T1 T9 w M" -> Index Scan using fki_parcel_application__parcel on parcel_application ord_app (cost=0.00..508.50 rows=3218 width=74) (actual time=0.019..6.952 rows=3218 loops=1)"
# G d V" r$ T- O6 {" -> Index Scan using fki_mlist__parcel_application on mlist m (cost=0.00..1177.29 rows=1 width=74) (actual time=66.900..69.060 rows=0 loops=3218)"
& L: U7 M5 y" u3 Y* S. n ~" Index Cond: ((m.parcel_application_rec_id)::text = (ord_app.parcel_application_rec_id)::text)"
: c. `& |# H7 |" Filter: (alternatives: SubPlan 1 or hashed SubPlan 2)"* h6 W, @' W0 x. o- s1 Z+ K( i
" SubPlan 1"
5 l1 ~9 r1 [- b" W" -> Nested Loop Semi Join (cost=3.49..5870.46 rows=5 width=0) (actual time=61.189..61.189 rows=0 loops=3631)", I$ H3 b& [$ Z4 z7 K1 R
" Join Filter: ((md.mlist_detail_rec_id)::text = (mdp.mlist_detail_rec_id)::text)"
% b# H" W3 l4 D( a; V+ k" -> Index Scan using fki_mlist_detail__mlist on mlist_detail md (cost=0.00..24.31 rows=5 width=37) (actual time=0.014..0.023 rows=4 loops=3631)"
5 ~) d9 ~, A" J" Index Cond: ((mlist_rec_id)::text = ($0)::text)"
# V8 Q& `" n6 y- l+ Z: c& y3 W" -> Hash Join (cost=3.49..1163.57 rows=676 width=37) (actual time=0.004..16.045 rows=1865 loops=13473)"
$ ~* l2 ?6 N# h# l6 f% B" Hash Cond: ((mdp.parameter_rec_id)::text = (p.parameter_rec_id)::text)"5 X# |/ E+ E7 l; b3 f
" -> Seq Scan on mlist_detail_parameter mdp (cost=0.00..1013.87 rows=37187 width=74) (actual time=0.001..5.935 rows=36353 loops=13473)"5 B, C& C. B1 z
" -> Hash (cost=3.48..3.48 rows=1 width=37) (actual time=1.646..1.646 rows=1 loops=1)") P V9 ~8 ^4 v8 U1 \, A
" -> Seq Scan on parameter p (cost=0.00..3.48 rows=1 width=37) (actual time=1.397..1.642 rows=1 loops=1)"
1 |/ F/ u9 V$ V6 n+ a% M5 r, i" Filter: (to_tsvector(regexp_replace((parameter)::text, '[\\(\\)\\!\\.\\/,\\-\\?]+'::text, ' '::text, 'g'::text)) @@ plainto_tsquery('cadmium'::text))"2 I! t- ]8 b% T2 w; {
" SubPlan 2"! A( v/ K4 s& K* C* |( {
" -> Hash Join (cost=1180.47..3241.32 rows=15198 width=37) (never executed)"
2 W9 Y6 _) e' Z+ M+ g5 Z( u" Hash Cond: ((md.mlist_detail_rec_id)::text = (mdp.mlist_detail_rec_id)::text)"
9 k- q" |6 [3 F% w$ y6 r" -> Seq Scan on mlist_detail md (cost=0.00..1987.79 rows=17679 width=74) (never executed)", o8 { G0 Y9 f# K1 C0 m4 b8 B/ `
" -> Hash (cost=1172.02..1172.02 rows=676 width=37) (never executed)"0 \, j8 V2 X' F2 c9 G8 i+ t
" -> HashAggregate (cost=1165.26..1172.02 rows=676 width=37) (never executed)"- k. x( d& |, m# m0 ~! f, {- B
" -> Hash Join (cost=3.49..1163.57 rows=676 width=37) (never executed)"
. o0 S" \/ o/ W7 _5 _" Hash Cond: ((mdp.parameter_rec_id)::text = (p.parameter_rec_id)::text)"8 B7 |( e2 R x# ]% `
" -> Seq Scan on mlist_detail_parameter mdp (cost=0.00..1013.87 rows=37187 width=74) (never executed)". B3 S% j9 `+ Q' U* K3 o7 g
" -> Hash (cost=3.48..3.48 rows=1 width=37) (never executed)"
3 S, C/ T: @7 _2 @" -> Seq Scan on parameter p (cost=0.00..3.48 rows=1 width=37) (never executed)"
& h" y9 V, y0 r6 k( S9 }- h" Filter: (to_tsvector(regexp_replace((parameter)::text, '[\\(\\)\\!\\.\\/,\\-\\?]+'::text, ' '::text, 'g'::text)) @@ plainto_tsquery('cadmium'::text))") L$ i `6 e. O* Q- i' z1 S
" -> Index Scan using fki_tlist__mlist on tlist t (cost=0.00..0.63 rows=1 width=37) (actual time=0.015..0.015 rows=1 loops=715)"
. U/ Z& U- V' O" Index Cond: ((t.mlist_rec_id)::text = (m.mlist_rec_id)::text)"1 W; x# B. e2 B
" -> Index Scan using pk_parcel on parcel ord (cost=0.00..338.49 rows=3087 width=74) (actual time=0.008..3.312 rows=3146 loops=1)". b e$ V3 ?4 [
" -> Index Scan using pk_company on company c (cost=0.00..0.29 rows=1 width=71) (actual time=0.009..0.009 rows=1 loops=715)"* O2 e3 v& p9 h) s
" Index Cond: ((c.company_rec_id)::text = (ord.client_rec_id)::text)"2 Z# d2 d+ O1 S- T5 P
"Total runtime: 222284.584 ms"! j f& A# ~, @; V% S. h
当我将其转换为左连接。- w" Z4 Z- K/ T8 e9 e& ~" v
explain analyze
9 K; x% G( M( l/ G! dselect
& u r/ o1 Q2 I* zc.company_rec_id, * d0 ]1 q4 [2 Z
c.the_company_code , T! D5 L- p+ Y% s- _
c.company) {7 b5 k% V$ I% F5 Q
from ( I2 b0 h7 Z; O6 h# S$ w
tlist t
8 ~6 ?/ x8 i( _0 S; K2 Z-- it is questionable why this query become fast when using left join, the most natural query is inner join...
v! |% Z7 D- H t# p5 wleft join mlist m using(mlist_rec_id). P9 _7 U$ o. `9 s
left join parcel_application ord_app using(parcel_application_rec_id)3 W7 t/ }1 s1 ~4 Z8 l0 x' N
left join parcel ord using(parcel_rec_id)
: g- g! |8 I3 M9 c! Fleft join company c on c.company_rec_id = ord.client_rec_id6 {7 a! N8 W0 I
-- ...questionable: d8 t1 v1 q, C5 a. h: B, X9 ?9 M- o
where 9 J5 x: J' z+ V; o, _5 q, V& I% ~7 B
(
& o2 a8 s ?+ P 'cadmium' = ''5 s% g( O6 g Z/ w( W
or- v% l# }+ i) B" s' v
exists( n. ]* z: U" M4 a! A
(
5 U* p- Y7 T" o1 d select *
# K( t6 {4 m7 |0 q6 `& t from mlist_detail md0 f% f, z1 ~2 y( Z+ d; T, g
where
) R3 `+ [; z( D7 P D7 D# z md.mlist_rec_id = m.mlist_rec_id / X7 J) R+ C0 J2 s
and exists g# O& G4 T7 C8 ?3 N7 G' a
(
( M8 E1 \6 _$ w' v" S select *
* e9 n( w& t- Z! r8 a' l. [- Z! Z from mlist_detail_parameter mdp 5 K n$ H$ P" N: }3 Q D) X
join parameter p using(parameter_rec_id)6 J3 e. w$ y8 s1 H
where mdp.mlist_detail_rec_id = md.mlist_detail_rec_id ) Y; i' j4 S3 ?$ d/ K N( ^
and to_tsvector(extract_words(p.parameter)) @@ plainto_tsquery(extract_words('cadmium'))
7 q3 N) W7 r% r2 |9 l! N7 N0 o )2 b1 G) r6 P. a/ r+ Z# e6 j
), Q E5 o% e5 }8 e) T0 p3 i5 b
)! c; I) g* d9 x
group by c.company_rec_id, ) a9 c7 k7 b5 K8 G1 x: D2 q
c.the_company_code, c.company
1 W) I' A% s1 S# T5 G( h. ]…变得很快：; e% H% ~8 J- X& {/ U2 j8 s0 K
"HashAggregate (cost=4276494.40..4276497.35 rows=295 width=71) (actual time=71.299..71.309 rows=84 loops=1)"5 x- x, A& Z" u' [' z4 Q) v4 }. W' B
" -> Nested Loop Left Join (cost=0.00..4276480.78 rows=1816 width=71) (actual time=35.271..70.776 rows=715 loops=1)"9 a" B0 O0 z4 X
" -> Nested Loop Left Join (cost=0.00..4275939.00 rows=1816 width=37) (actual time=35.260..66.157 rows=715 loops=1)"
9 H0 k& Y8 S1 \9 M" -> Nested Loop Left Join (cost=0.00..4275263.92 rows=1816 width=37) (actual time=35.243..60.185 rows=715 loops=1)"
6 h. d5 l/ {$ n& O2 L& Y9 ~" -> Merge Left Join (cost=0.00..4274523.56 rows=1816 width=37) (actual time=35.226..54.834 rows=715 loops=1)"
8 [1 p7 f! _" o" R% g" Merge Cond: ((t.mlist_rec_id)::text = (m.mlist_rec_id)::text)"
1 v) l9 ~& y; c* _- {: n" Filter: (alternatives: SubPlan 1 or hashed SubPlan 2)"9 G: Y8 a% b% k- g! i" Q1 B: _
" -> Index Scan using fki_tlist__mlist on tlist t (cost=0.00..710.72 rows=3631 width=37) (actual time=0.011..1.947 rows=3631 loops=1)"
( c6 p8 F# F: m4 F! ~) ~" -> Index Scan using pk_mlist on mlist m (cost=0.00..482.71 rows=3631 width=74) (actual time=0.008..1.829 rows=3631 loops=1)"" J7 g( M# v+ z( P; _
" SubPlan 1"
. b0 ?) K) c, O6 n" -> Nested Loop Semi Join (cost=3.49..5870.46 rows=5 width=0) (never executed)"+ z6 z Q6 R- s# m/ u7 }1 p- J
" Join Filter: ((md.mlist_detail_rec_id)::text = (mdp.mlist_detail_rec_id)::text)"
9 s5 _% T6 Q" c0 d7 |6 n; {" -> Index Scan using fki_mlist_detail__mlist on mlist_detail md (cost=0.00..24.31 rows=5 width=37) (never executed)"7 d6 r' b, T" U# Z
" Index Cond: ((mlist_rec_id)::text = ($0)::text)"
& g# H9 X; H0 i v" -> Hash Join (cost=3.49..1163.57 rows=676 width=37) (never executed)"& y* }* e. u2 z0 r* l; E
" Hash Cond: ((mdp.parameter_rec_id)::text = (p.parameter_rec_id)::text)"
R3 Y7 t0 o" I) ?, T" -> Seq Scan on mlist_detail_parameter mdp (cost=0.00..1013.87 rows=37187 width=74) (never executed)"0 R4 Y7 M) H; ?9 a, j# k$ |
" -> Hash (cost=3.48..3.48 rows=1 width=37) (never executed)", `7 V2 \$ q1 k% z @: M
" -> Seq Scan on parameter p (cost=0.00..3.48 rows=1 width=37) (never executed)"
6 p& m6 G- p& s+ O: _! R" Filter: (to_tsvector(regexp_replace((parameter)::text, '[\\(\\)\\!\\.\\/,\\-\\?]+'::text, ' '::text, 'g'::text)) @@ plainto_tsquery('cadmium'::text))"
( t; c# J3 y3 c" A* b" u" SubPlan 2"
% ?, w0 ^+ ~8 Q, O! d: A; N( S" -> Hash Join (cost=1180.47..3241.32 rows=15198 width=37) (actual time=16.478..34.359 rows=1918 loops=1)"3 C8 j4 J; W8 i+ ?7 m P
" Hash Cond: ((md.mlist_detail_rec_id)::text = (mdp.mlist_detail_rec_id)::text)"
1 r) d! p5 [. }# s" -> Seq Scan on mlist_detail md (cost=0.00..1987.79 rows=17679 width=74) (actual time=0.004..3.013 rows=17679 loops=1)"
2 O, v+ P# ]$ v7 j) A6 J1 b" -> Hash (cost=1172.02..1172.02 rows=676 width=37) (actual time=16.461..16.461 rows=1918 loops=1)"' u0 R2 @: [- t1 y* H p5 [
" -> HashAggregate (cost=1165.26..1172.02 rows=676 width=37) (actual time=15.790..16.037 rows=1918 loops=1)"
/ j) m% T$ D1 P9 ~, [7 \3 }8 c" -> Hash Join (cost=3.49..1163.57 rows=676 width=37) (actual time=1.613..15.037 rows=1918 loops=1)"
% z1 b, P* t* ~* n$ |* X, e' h" Hash Cond: ((mdp.parameter_rec_id)::text = (p.parameter_rec_id)::text)"9 O. n; W2 F5 O; s1 }6 w( G
" -> Seq Scan on mlist_detail_parameter mdp (cost=0.00..1013.87 rows=37187 width=74) (actual time=0.002..4.624 rows=37187 loops=1)"; M' _) N1 J$ S
" -> Hash (cost=3.48..3.48 rows=1 width=37) (actual time=1.593..1.593 rows=1 loops=1)"
T9 [ b$ S' s- S+ J& t* M# c" -> Seq Scan on parameter p (cost=0.00..3.48 rows=1 width=37) (actual time=1.311..1.588 rows=1 loops=1)"& a# \3 i+ _6 A2 i
" Filter: (to_tsvector(regexp_replace((parameter)::text, '[\\(\\)\\!\\.\\/,\\-\\?]+'::text, ' '::text, 'g'::text)) @@ plainto_tsquery('cadmium'::text))"# ^& m1 b1 {; C V \3 n
" -> Index Scan using pk_parcel_application on parcel_application ord_app (cost=0.00..0.40 rows=1 width=74) (actual time=0.007..0.007 rows=1 loops=715)"
, z2 t; n! h6 L. D! V" Index Cond: ((m.parcel_application_rec_id)::text = (ord_app.parcel_application_rec_id)::text)" M4 X. y4 s# r( I6 E" j6 K( X
" -> Index Scan using pk_parcel on parcel ord (cost=0.00..0.36 rows=1 width=74) (actual time=0.007..0.008 rows=1 loops=715)"
' v4 f" b, K9 x) K" Index Cond: ((ord_app.parcel_rec_id)::text = (ord.parcel_rec_id)::text)"7 D* }. B4 E G, x
" -> Index Scan using pk_company on company c (cost=0.00..0.29 rows=1 width=71) (actual time=0.006..0.006 rows=1 loops=715)"* P0 P2 a; ?$ {: X4 s3 G
" Index Cond: ((c.company_rec_id)::text = (ord.client_rec_id)::text)"
1 Q+ i! H4 H# f% L; B+ \"Total runtime: 71.504 ms"
( C" s6 @/ O/ i如果执行此操作，它将使用其自然查询（即INNER JOIN）（请注意mlist_detail_parameter和参数表之间的多余EXISTS）。- V3 ^9 k) y G
explain analyze2 ^2 I7 {. P+ \* `
select
% t, O* q% g- E3 V/ ]% Cc.company_rec_id, 8 b0 S0 o! ?, |8 e
c.the_company_code ,
& |3 f3 A& g. Q+ m) h' Qc.company3 k4 G7 R: B _
from " u& E3 X; f) L1 l2 c T
tlist t
, z& b `- a) Q/ A3 Tjoin mlist m using(mlist_rec_id)/ ?/ \# N2 F9 K8 q# F3 s; v7 ^5 B& c
join parcel_application ord_app using(parcel_application_rec_id)
9 }& J* E3 H- @join parcel ord using(parcel_rec_id)
* Q- ?$ S0 L' h1 ]- l4 |. J4 ]/ ]join company c on c.company_rec_id = ord.client_rec_id0 f2 L8 i2 `0 s" u5 r1 R
where
$ U, i& U4 Z/ g/ W! o! q(
* f; W) N* h" T+ n2 j 'cadmium' = ''
2 Q4 V$ @. I; Q1 B or% [' `2 {2 Q2 u0 G" { x9 }8 m
exists
2 N2 ^# U. Q9 F8 g$ _0 g- l ( E0 _' V# Z* N' e9 H
select *
2 D% ]) c, t- w, \ from mlist_detail md, P9 }5 n% ~% L" Q; P0 }
where 2 r$ C3 c8 K! [1 [# M! V2 Z
md.mlist_rec_id = m.mlist_rec_id
) Y! @5 \5 _2 J- A and exists
6 m& K* w" @ z) I (2 t6 ^$ Z; T+ I4 D4 g6 l
select * from mlist_detail_parameter mdp8 C% R/ ]* q3 P1 V8 r) C, ]
where mdp.mlist_detail_rec_id = md.mlist_detail_rec_id
. Q1 s4 W$ O: n+ A6 h -- Notice that there is no joining of parameter TO mlist_detail_parameter; We use EXISTS instead, which looks very superfluous9 u, l8 j) }% b0 X1 {
and exists
9 W* Z. m- s% i$ D4 L0 t. G4 g (3 {* ^$ s2 I5 J' Z6 o* m
select * from parameter p
& D7 Q* i6 M# U' Y0 H where parameter_rec_id = mdp.parameter_rec_id3 `6 ^$ V: i$ o/ h( W/ ~9 L4 P6 v
and to_tsvector(extract_words(p.parameter)) @@ plainto_tsquery(extract_words('cadmium'))
$ U4 ^: C7 ?! L+ e7 f& e )
, I5 M- S1 i% ^$ ^ )- Z9 g e) M$ i% c7 ~5 }
)
# k: z% i8 _! l& s)
9 J) E4 `+ Y, O, e) b" pgroup by c.company_rec_id,
7 m* q4 h3 B' |& {. x$ r c.the_company_code, c.company# V' ~# ?/ w7 z; V4 V! s# R, [! ~2 H, E; b
…， 有用：
$ E. `7 R* c/ N: ~, D"HashAggregate (cost=76424.17..76427.12 rows=295 width=71) (actual time=398.906..398.932 rows=84 loops=1)"
5 y- M: m& o% L0 l9 Y8 d$ X% ]" -> Nested Loop (cost=0.00..76410.55 rows=1816 width=71) (actual time=52.529..398.171 rows=715 loops=1)"
9 O2 Q. c+ s3 u$ u3 B5 i" E* z0 R" -> Merge Join (cost=0.00..75868.77 rows=1816 width=37) (actual time=45.106..385.128 rows=715 loops=1)"
1 N& J& ?2 i* ? E& R" Merge Cond: ((ord_app.parcel_rec_id)::text = (ord.parcel_rec_id)::text)"
( Z2 h' i& s( `- Q) Q6 s" -> Nested Loop (cost=0.00..75499.86 rows=1816 width=37) (actual time=41.745..374.833 rows=715 loops=1)"
\! x& \8 D5 m; Y; h- y ^, W" -> Nested Loop (cost=0.00..74333.34 rows=1816 width=74) (actual time=31.483..302.607 rows=715 loops=1)"2 u; S. k& Y& N$ k+ M2 ?7 e+ Y
" -> Index Scan using fki_parcel_application__parcel on parcel_application ord_app (cost=0.00..508.50 rows=3218 width=74) (actual time=2.459..5.797 rows=3218 loops=1)"
& I0 b, @( p% e. t' G2 V# f" -> Index Scan using fki_mlist__parcel_application on mlist m (cost=0.00..22.93 rows=1 width=74) (actual time=0.089..0.092 rows=0 loops=3218)"
' Y+ r% ~) `3 b: A" Index Cond: ((m.parcel_application_rec_id)::text = (ord_app.parcel_application_rec_id)::text)"" Q Y5 J0 Y- K8 v
" Filter: (alternatives: SubPlan 3 or hashed SubPlan 6)"
, T6 y: m# o& s- W; Y" SubPlan 3". y! g5 I" K% ]
" -> Nested Loop Semi Join (cost=0.00..112.64 rows=5 width=0) (actual time=0.071..0.071 rows=0 loops=3631)"7 K$ Y( ^ G5 D6 |
" -> Index Scan using fki_mlist_detail__mlist on mlist_detail md (cost=0.00..24.31 rows=5 width=37) (actual time=0.019..0.023 rows=4 loops=3631)"
; ^4 Q" W) E/ B- |& I3 J" Index Cond: ((mlist_rec_id)::text = ($0)::text)"& @ q9 S$ D" g- t
" -> Index Scan using fki_mlist_detail_parameter__mlist_detail on mlist_detail_parameter mdp (cost=0.00..19.63 rows=1 width=37) (actual time=0.012..0.012 rows=0 loops=13473)"' n$ u9 `, X/ k3 m$ b- H
" Index Cond: ((mdp.mlist_detail_rec_id)::text = (md.mlist_detail_rec_id)::text)"
) [/ j: k6 c3 N" Filter: (alternatives: SubPlan 1 or hashed SubPlan 2)"
, L- h% |- @6 x0 { r" SubPlan 1"
5 B! n( q, \' r& x$ ~ q$ q" -> Seq Scan on parameter p (cost=0.00..3.67 rows=1 width=0) (never executed)"% X4 | M& N- [; J6 x+ j1 s7 I f. K3 A
" Filter: (((parameter_rec_id)::text = ($1)::text) AND (to_tsvector(regexp_replace((parameter)::text, '[\\(\\)\\!\\.\\/,\\-\\?]+'::text, ' '::text, 'g'::text)) @@ plainto_tsquery('cadmium'::text)))"- W, u# A5 G% Q1 p5 v
" SubPlan 2"5 C* y8 ?$ z5 i: P
" -> Seq Scan on parameter p (cost=0.00..3.48 rows=1 width=37) (actual time=1.475..1.774 rows=1 loops=1)"
. B7 q. F9 p" P" Filter: (to_tsvector(regexp_replace((parameter)::text, '[\\(\\)\\!\\.\\/,\\-\\?]+'::text, ' '::text, 'g'::text)) @@ plainto_tsquery('cadmium'::text))"2 E9 X, ^: M$ K0 H2 Y4 n' N3 s
" SubPlan 6". [) ^$ q6 a1 |
" -> Nested Loop Semi Join (cost=0.00..128665.02 rows=15198 width=37) (never executed)"- }8 T% m" k% N* L
" -> Seq Scan on mlist_detail md (cost=0.00..1987.79 rows=17679 width=74) (never executed)"* J0 `1 A8 I7 I: v$ @0 U
" -> Index Scan using fki_mlist_detail_parameter__mlist_detail on mlist_detail_parameter mdp (cost=0.00..7.83 rows=1 width=37) (never executed)"8 b2 l2 v& a! h% J X& n
" Index Cond: ((mdp.mlist_detail_rec_id)::text = (md.mlist_detail_rec_id)::text)"
2 u* q9 x8 d5 r# W" Filter: (alternatives: SubPlan 4 or hashed SubPlan 5)"
; B) U. k3 W U1 n* i- i" SubPlan 4"
9 j! R$ X s U8 s) F" -> Seq Scan on parameter p (cost=0.00..3.67 rows=1 width=0) (never executed)". j7 s( d, U/ H% [3 ]
" Filter: (((parameter_rec_id)::text = ($1)::text) AND (to_tsvector(regexp_replace((parameter)::text, '[\\(\\)\\!\\.\\/,\\-\\?]+'::text, ' '::text, 'g'::text)) @@ plainto_tsquery('cadmium'::text)))"% H9 U1 I! C! V( K7 t3 ?
" SubPlan 5"$ m# o0 X4 h) I5 w
" -> Seq Scan on parameter p (cost=0.00..3.48 rows=1 width=37) (never executed)") C% ^$ z$ V: P# H! a$ M% K: e
" Filter: (to_tsvector(regexp_replace((parameter)::text, '[\\(\\)\\!\\.\\/,\\-\\?]+'::text, ' '::text, 'g'::text)) @@ plainto_tsquery('cadmium'::text))"& y! U: u! G4 s+ z
" -> Index Scan using fki_tlist__mlist on tlist t (cost=0.00..0.63 rows=1 width=37) (actual time=0.100..0.100 rows=1 loops=715)"
; c: t0 k) v' `" Index Cond: ((t.mlist_rec_id)::text = (m.mlist_rec_id)::text)"
1 i* D) c2 O3 H s7 A$ V0 R" -> Index Scan using pk_parcel on parcel ord (cost=0.00..338.49 rows=3087 width=74) (actual time=3.277..5.920 rows=3146 loops=1)"
- ~0 W! z- Y/ t" -> Index Scan using pk_company on company c (cost=0.00..0.29 rows=1 width=71) (actual time=0.017..0.017 rows=1 loops=715)"
, g0 e% I- p7 E# }! g4 ~" Index Cond: ((c.company_rec_id)::text = (ord.client_rec_id)::text)"
5 y6 d( W/ [) {# |+ j" ~' ["Total runtime: 399.649 ms"
' {: Q3 Z9 V0 _; F, m0 f即使速度很快，但我认为使用多余的EXISTS是不自然的。# @! \# L0 C& N( \
and exists0 O% e3 F, Y2 K- U7 T
(
, y3 D1 N7 t; ~- g select * from mlist_detail_parameter mdp
* N7 H. z2 k3 q. q6 A where mdp.mlist_detail_rec_id = md.mlist_detail_rec_id
( J9 z. I' y8 A. `. t) E) E" o -- Notice that there is no joining of parameter to mlist_detail_parameter, we use EXISTS instead
1 w, V# `2 L! E7 x k; ]! a) m N and exists -- SUPERFLUOUS
& @( M* |- v# t+ h* Q3 J* c, y0 N$ y (
6 @! e/ a( b/ d select * from parameter p
( j) W) Y( B: i% d) ]6 K where parameter_rec_id = mdp.parameter_rec_id
8 y9 u+ a4 J! q) G* b! K( P and to_tsvector(extract_words(p.parameter)) @@ plainto_tsquery(extract_words('cadmium'))
8 b( t2 r( Z2 p- C# m# u )
& o5 w0 C* H+ E; N); ]" N, K6 r4 d$ K& [* k; k- E$ E
…，何时加入将执行以下操作：- d" r) o0 Z) g7 j& z! a
and exists
" h4 _' i0 W5 x( E(! [5 [, b7 z) E& E
select *
- f. k: z4 J- R from mlist_detail_parameter mdp
5 s2 F* \$ J( E3 z% k) F join parameter p using(parameter_rec_id)8 Y% y+ N3 {: v
where mdp.mlist_detail_rec_id = md.mlist_detail_rec_id
8 `8 M" a0 e6 \% e! c; z( `) { and to_tsvector(extract_words(p.parameter)) @@ plainto_tsquery(extract_words('cadmium'))
6 _1 P' S* ?! E, K) O# N/ d)
* I+ j# q9 ]1 |$ k9 `5 d# e( r" C7 |我的问题是为什么最自然的查询（即使用INNER JOIN（而不是LEFT JOIN））非常慢。我可以选择不使用LEFT
+ W4 w# d( n ]- Z3 UJOIN的查询，但是必须在mlist_detail_parameter和参数表上使用多余的EXISTS，而不是使用直接联接。/ V2 o3 K; i5 v) k( h4 K7 B) B; U
我发现最甜蜜的表现是只使用了LEFT JOIN，但是我认为 盲目地 应用LEFT JOIN可能会导致我进行货邪教编程2 w9 {' u, t# ~- S5 E+ m% `" `6 n
[编辑：2010-07-06]" M5 r+ @* m/ d/ a0 P" b
达恩，我现在是IN查询的粉丝，我将EXISTS查询转换为IN查询，现在它的运行速度惊人。
' P) N/ ], F: {这个查询。
# Z* l c9 R$ `" f+ V6 e9 Sexplain analyze0 ?8 t. t! e& L1 f5 j1 w
select
4 q8 ~4 b r+ ^, ]# y$ cc.company_rec_id,
. r3 _' ~- z9 Y- r1 r9 {: bc.the_company_code ,
8 F3 q! Z, [, t Fc.company; r6 {; q+ E9 |5 i
from 8 o4 P! F( ?6 F0 v- ?
tlist t; A4 b) d% p, _9 f4 c
-- it is questionable why this query become fast when using left join, the most natural query is inner join... o f% d) x+ ` o/ k
join mlist m using(mlist_rec_id)
. d. b) R1 O; w# ~join parcel_application ord_app using(parcel_application_rec_id)
9 j+ S, Y! {1 f: djoin parcel ord using(parcel_rec_id)
. Y- }" {: f$ S; cjoin company c on c.company_rec_id = ord.client_rec_id& Y+ e7 e5 s" s& m$ W
-- ...questionable, z4 ^, K3 R* O9 E: p: O2 {' X
where 1 C9 A3 T$ ]* T& g0 F8 g
(9 \7 v# s U& Q" t+ \9 s) r
'cadmium' = ''
/ Q' i6 B1 @/ k/ D" F2 R or/ l/ O; b% w: | w! m1 v
mlist_rec_id in
; O2 G A9 V$ e (! A( E- f/ {5 S1 M1 C
select mlist_rec_id9 B# V; H6 a5 C6 f: e5 K2 H0 a
from mlist_detail md
4 ?' a U! k( B% c9 Y; m7 C where 5 d( V. L7 _( k0 W8 I
mlist_detail_rec_id in
, [/ J9 N! W) X( R7 k (% J9 c$ A4 B6 ~
select mlist_detail_rec_id/ L) W% r/ z0 z* ~0 b
from mlist_detail_parameter mdp ! t; o! m) m9 v! Z2 y" r4 z
join parameter p using(parameter_rec_id)! U4 K2 D0 G" f$ c2 C$ e
where to_tsvector(extract_words(p.parameter)) @@ plainto_tsquery(extract_words('cadmium')) 7 N7 }2 R3 k) E `: s' w
)3 l2 S5 b% @6 ~! r& U# U* P
)
5 J! a! E+ V9 i8 r2 E% L; D+ u)9 ?$ {, ]; B5 Q! g
group by c.company_rec_id, ; f' C* s1 U' [$ M* T. |/ L
c.the_company_code, c.company
3 R ~4 v9 m8 m, d…具有以下执行计划（0.037秒）：* a6 c1 o7 Y* k
"HashAggregate (cost=4045.22..4048.17 rows=295 width=71) (actual time=37.117..37.128 rows=84 loops=1)"
& X/ |- z( X5 R1 x) o" -> Hash Join (cost=3734.44..4031.60 rows=1816 width=71) (actual time=33.610..36.650 rows=715 loops=1)"
' d. M: [) m( v1 @/ ?: l& ], u# h" Hash Cond: ((ord.client_rec_id)::text = (c.company_rec_id)::text)"+ p6 e3 {; P" E2 _5 `
" -> Hash Join (cost=3718.80..3990.99 rows=1816 width=37) (actual time=33.448..36.150 rows=715 loops=1)": T/ I0 M1 }; }$ P& M g
" Hash Cond: ((ord_app.parcel_rec_id)::text = (ord.parcel_rec_id)::text)"! i0 Z. r) E5 u8 C. s1 e
" -> Hash Join (cost=3601.34..3844.02 rows=1816 width=37) (actual time=32.096..34.360 rows=715 loops=1)": s' j; B, L$ S' A% p
" Hash Cond: ((m.parcel_application_rec_id)::text = (ord_app.parcel_application_rec_id)::text)"# f @3 _: {( ], C" ?* Y* d
" -> Hash Join (cost=3438.94..3652.11 rows=1816 width=37) (actual time=30.578..32.370 rows=715 loops=1)"* c" x3 b1 ^6 n
" Hash Cond: ((t.mlist_rec_id)::text = (m.mlist_rec_id)::text)"
/ G( s, D/ K, f$ K2 y2 @" -> Seq Scan on tlist t (cost=3278.24..3459.63 rows=1816 width=37) (actual time=28.930..30.219 rows=715 loops=1)"
2 V1 [1 Q& c7 E3 s" Filter: (hashed SubPlan 1)"8 C( e, j" d! B S* g& `
" SubPlan 1"
5 F* a, @6 J$ m% r, }" -> Hash Join (cost=1179.63..3240.24 rows=15201 width=37) (actual time=17.259..27.282 rows=1918 loops=1)"
' B n9 ^- `( q" Hash Cond: ((md.mlist_detail_rec_id)::text = (mdp.mlist_detail_rec_id)::text)"
& E1 W, E/ A, P9 _! D, a( h" -> Seq Scan on mlist_detail md (cost=0.00..1987.79 rows=17679 width=74) (actual time=0.002..4.160 rows=17679 loops=1)"
, e# ?/ x+ @# ^" -> Hash (cost=1171.48..1171.48 rows=652 width=37) (actual time=17.236..17.236 rows=1918 loops=1)"
, i, p3 l$ s) E" -> HashAggregate (cost=1164.96..1171.48 rows=652 width=37) (actual time=16.290..16.692 rows=1918 loops=1)"
" n, `3 r7 }' X ~/ Q' l" -> Hash Join (cost=3.49..1163.33 rows=652 width=37) (actual time=0.868..15.420 rows=1918 loops=1)"
! f- h5 Q4 G1 b8 U2 m" Hash Cond: ((mdp.parameter_rec_id)::text = (p.parameter_rec_id)::text)"
! F1 v! a( Z: m" -> Seq Scan on mlist_detail_parameter mdp (cost=0.00..1013.87 rows=37187 width=74) (actual time=0.002..5.397 rows=37187 loops=1)"' S6 C8 w5 F0 I& H2 Y) U5 q1 X5 }
" -> Hash (cost=3.48..3.48 rows=1 width=37) (actual time=0.850..0.850 rows=1 loops=1)"# \! M. W, t5 b9 K; F
" -> Seq Scan on parameter p (cost=0.00..3.48 rows=1 width=37) (actual time=0.719..0.848 rows=1 loops=1)"4 @, B* W) F1 Q: ~2 G
" Filter: (to_tsvector(regexp_replace((parameter)::text, '[\\(\\)\\!\\.\\/,\\-\\?]+'::text, ' '::text, 'g'::text)) @@ plainto_tsquery('cadmium'::text))"
- k- f! z4 x; b. I- S" -> Hash (cost=115.31..115.31 rows=3631 width=74) (actual time=1.632..1.632 rows=3631 loops=1)"
/ [2 R2 c) g8 M% ]" -> Seq Scan on mlist m (cost=0.00..115.31 rows=3631 width=74) (actual time=0.002..0.569 rows=3631 loops=1)"# R1 c* i( J' Z
" -> Hash (cost=122.18..122.18 rows=3218 width=74) (actual time=1.507..1.507 rows=3218 loops=1)". r* z7 f% X- U+ V& J# c' b
" -> Seq Scan on parcel_application ord_app (cost=0.00..122.18 rows=3218 width=74) (actual time=0.002..0.531 rows=3218 loops=1)"
# R6 X* z: ?" l4 z/ d8 G8 s" -> Hash (cost=78.87..78.87 rows=3087 width=74) (actual time=1.345..1.345 rows=3087 loops=1)"
' r( I+ E: I' k# B" K0 d" W( U8 U" -> Seq Scan on parcel ord (cost=0.00..78.87 rows=3087 width=74) (actual time=0.002..0.469 rows=3087 loops=1)"
5 r3 w! R3 h7 d2 _" -> Hash (cost=11.95..11.95 rows=295 width=71) (actual time=0.155..0.155 rows=295 loops=1)"
4 Y/ s6 S3 I0 F/ ^5 j" -> Seq Scan on company c (cost=0.00..11.95 rows=295 width=71) (actual time=0.003..0.063 rows=295 loops=1)"
( [* u) ^, x/ ^4 E"Total runtime: 37.240 ms"4 R8 \$ j4 e7 ]8 R* R( A1 E& m
一些想法。我认为，最重要的是，Postgresql对EXISTS子句进行了错误的优化，我不希望EXISTS子句本质上比IN慢。实际上，我记得我之前通过将IN子句转换为EXISTS子句优化了一个非性能查询（我认为是8.3或8.2）。( F, [) ]9 p5 ^4 S8 c o
+ N* N# D6 v$ K2 N6 U! _
解决方案:
2 I F( q$ E% u6 h( a
7 F9 `# R8 L/ P7 h. I$ r
7 R& T+ K' j8 \* z
- ?9 B; l: V; `( T/ {/ X. ^) U （按照指示，我将部分评论放入答案中，因为它可以解决问题）( D! M* e! D. k* g" K3 T; y3 w7 V
将EXISTS表达式转换为IN表达式。+ e) s/ B/ L" z0 x! `6 D) y
在这种情况下，这种方法效果更好，因为从包含您最大限制因素（全文搜索查找）的查询开始，现在将从“由内而外”对查询进行有效的评估。该查询将返回一小组行，可以直接针对外部查询的主键（在（SELECTX …）中的WHEREx）进行查询，而不是针对每个的值调用“内部”查询外部查询（如果我正确阅读，则查询原始情况下的所有值）。在这里，EXISTS方法导致嵌套循环（对一个查询的另一个值进行一次求值），而使用散列连接（在许多情况下，即使不是大多数情况下，效率更高的执行方法）下的IN方法也是如此。
- F& i0 p# R3 m: w" l8 D7 l6 x- p注意，使用EXISTS方法，有四个嵌套循环，每个循环执行至少3,000次。那笔费用加起来。虽然这不是直接比较，但是您可以像处理应用程序代码中的FOR循环那样对待嵌套循环：每次调用内部循环时，big-
' q$ N# V: P) s3 s* g6 N( K* DO估计都会增加一个数量级：O（n）到O（n ^ 2）到O（n ^ 3）等
1 L- |, n4 r3 B# I8 s哈希联接更像是一个映射，其中两个数组同时执行，并对两个数组执行操作。这大致是线性的（O（n））。考虑将这些嵌套为加法器，这样它将从O（n）到O（2n）到O（3n），依此类推。
0 L `" n& l/ T6 {( a/ H0 n" f7 B- t2 J是的，是的，我知道这不是完全相同的事情，但是我要指出的是，具有多个嵌套循环通常表示查询计划很慢，并且比较这两种big-O样式可以使其更容易识别。
3 |( Z0 |1 `0 e( H0 Z1 s4 d6 w嵌套循环和EXISTS本身并不是邪恶的，但是在大多数情况下，存在一个基本过滤条件最终会影响所有内容（例如，问题中的全文本搜索），一个IN表达式（或者在
8 k1 x" V# G) P% p. }- `某些 情况下，一个适当的JOIN）可产生效率更高的计划。 |
|
技术问答
241 人阅读
|
0 人回复
|
2023-09-13
