Actual rows significantly more than estimated during many joins
| От | Andrew Beverley |
|---|---|
| Тема | Actual rows significantly more than estimated during many joins |
| Дата | |
| Msg-id | 20160604120754.c2a7dc03f89bfa1c154a5aeb@andybev.com обсуждение исходный текст |
| Ответы |
Re: Actual rows significantly more than estimated during
many joins
(Andrew Beverley <andy@andybev.com>)
Re: Actual rows significantly more than estimated during many joins (Andrew Beverley <andy@andybev.com>) |
| Список | pgsql-general |
Dear all,
I'm performing a query with many joins, with a WHERE condition on the
"root" table. As far as I am aware, each join is indexed, as is the
WHERE clause. To my simple mind, this is just a case of taking a set of
conditional indexed values, and then "adding on" the relevant indexed
data.
When I run EXPLAIN ANALYZE, I see that the actual query is scanning
significantly more rows for the join than was estimated. There is also
a huge number of loops for the joins. Why is this, and is there an easy
fix?
Rightly or wrongly I am trying to keep my software database-agnostic,
so it's an ORM generating the statement, so ideally I'd like a fix
without restructuring the statement significantly or using specialist
Pg syntax.
I've pasted the EXPLAIN and query below. Apologies for the long lines,
but I couldn't see any better way of showing it without pasting
on-line, and then it wouldn't be in the archives.
Any help or comments would be appreciated.
Thanks,
Andy
Sort (cost=3376.92..3377.07 rows=61 width=8) (actual time=59230.618..60650.868 rows=1048624 loops=1)
Sort Key: me.id
Sort Method: external sort Disk: 18464kB
Buffers: shared hit=13374435, temp read=2308 written=2308
-> Nested Loop Left Join (cost=220.91..3375.11 rows=61 width=8) (actual time=0.554..57363.104 rows=1048624
loops=1)
Buffers: shared hit=13374435
-> Nested Loop Left Join (cost=220.48..3295.38 rows=60 width=16) (actual time=0.540..41638.047 rows=1048624
loops=1)
Buffers: shared hit=9179928
-> Nested Loop Left Join (cost=220.06..3219.63 rows=57 width=16) (actual time=0.532..25942.789
rows=1048624loops=1)
Buffers: shared hit=4985421
-> Nested Loop Left Join (cost=219.63..3146.54 rows=55 width=16) (actual time=0.523..16013.263
rows=524336loops=1)
Buffers: shared hit=2888066
-> Nested Loop Left Join (cost=219.34..3126.10 rows=55 width=16) (actual
time=0.512..12234.358rows=262192 loops=1)
Buffers: shared hit=2101489
-> Nested Loop Left Join (cost=218.92..3053.01 rows=55 width=16) (actual
time=0.501..8255.675rows=262192 loops=1)
Buffers: shared hit=1052710
-> Nested Loop Left Join (cost=218.49..2911.40 rows=55 width=16) (actual
time=0.489..4132.432rows=131120 loops=1)
Buffers: shared hit=528208
-> Nested Loop Left Join (cost=218.06..2769.79 rows=55 width=16) (actual
time=0.478..2081.272rows=65584 loops=1)
Buffers: shared hit=265850
-> Nested Loop Left Join (cost=217.63..2628.18 rows=55 width=16)
(actualtime=0.466..1049.648 rows=32816 loops=1)
Buffers: shared hit=134564
-> Nested Loop Left Join (cost=217.21..2486.57 rows=55
width=16)(actual time=0.456..533.318 rows=16432 loops=1)
Buffers: shared hit=68814
-> Nested Loop Left Join (cost=216.78..2344.96 rows=55
width=16)(actual time=0.445..274.378 rows=8240 loops=1)
Buffers: shared hit=35832
-> Nested Loop Left Join (cost=216.35..2203.35
rows=55width=16) (actual time=0.433..143.907 rows=4144 loops=1)
Buffers: shared hit=19234
-> Nested Loop Left Join
(cost=215.92..2061.74rows=55 width=16) (actual time=0.423..77.844 rows=2096 loops=1)
Buffers: shared hit=10828
-> Nested Loop Left Join
(cost=215.50..1920.13rows=55 width=16) (actual time=0.413..44.077 rows=1072 loops=1)
Buffers: shared hit=6518
-> Nested Loop Left Join
(cost=215.07..1778.52rows=55 width=16) (actual time=0.403..26.580 rows=560 loops=1)
Buffers: shared hit=4256
-> Nested Loop Left Join
(cost=214.64..1636.91rows=55 width=16) (actual time=0.392..17.116 rows=304 loops=1)
Buffers: shared
hit=3018
-> Nested Loop Left
Join (cost=214.22..1563.38 rows=55 width=16) (actual time=0.378..13.003 rows=176 loops=1)
Buffers:
sharedhit=2273
-> Nested
LoopLeft Join (cost=213.79..1421.77 rows=55 width=16) (actual time=0.362..9.852 rows=112 loops=1)
Buffers:
sharedhit=1803
->
BitmapHeap Scan on current me (cost=210.93..297.25 rows=49 width=16) (actual time=0.245..0.349 rows=49 loops=1)
RecheckCond: ((id = 3472) OR (id = 3484) OR (id = 3510) OR (id = 3528) OR (id = 3553) OR (id = 3561) OR (id = 3571) OR
(id= 3583) OR (id = 3591) OR (id = 3603) OR (id = 3612) OR (id = 3631) OR (id = 3641) OR (id = 3665) OR (id = 3671) OR
(id= 3679) OR (id = 3693) OR (id = 3700) OR (id = 3708) OR (id = 3712) OR (id = 3742) OR (id = 3749) OR (id = 3758) OR
(id= 3768) OR (id = 3778) OR (id = 8275) OR (id = 8312) OR (id = 8351) OR (id = 8512) OR (id = 8532) OR (id = 8550) OR
(id= 8834) OR (id = 8859) OR (id = 14278) OR (id = 14674) OR (id = 14675) OR (id = 14676) OR (id = 14677) OR (id =
14678)OR (id = 14679) OR (id = 15036) OR (id = 15037) OR (id = 15039) OR (id = 15364) OR (id = 15395) OR (id = 15667)
OR(id = 16135) OR (id = 18430) OR (id = 18916))
HeapBlocks: exact=21
Buffers:shared hit=119
->
BitmapOr (cost=210.93..210.93 rows=49 width=0) (actual time=0.235..0.235 rows=0 loops=1)
Buffers: shared hit=98
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)
Index Cond: (id = 3472)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=1)
Index Cond: (id = 3484)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=1)
Index Cond: (id = 3510)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 3528)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 3553)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 3561)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=1)
Index Cond: (id = 3571)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 3583)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 3591)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
Index Cond: (id = 3603)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 3612)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 3631)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 3641)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
Index Cond: (id = 3665)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 3671)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 3679)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 3693)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 3700)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 3708)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
Index Cond: (id = 3712)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
Index Cond: (id = 3742)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 3749)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 3758)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 3768)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
Index Cond: (id = 3778)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 8275)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=1)
Index Cond: (id = 8312)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 8351)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 8512)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 8532)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
Index Cond: (id = 8550)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=1)
Index Cond: (id = 8834)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=1)
Index Cond: (id = 8859)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=1)
Index Cond: (id = 14278)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=1)
Index Cond: (id = 14674)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 14675)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 14676)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 14677)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 14678)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 14679)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=1)
Index Cond: (id = 15036)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 15037)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 15039)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 15364)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=1)
Index Cond: (id = 15395)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = 15667)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=1)
Index Cond: (id = 16135)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=1)
Index Cond: (id = 18430)
Buffers: shared hit=2
-> Bitmap Index Scan on current_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=1)
Index Cond: (id = 18916)
Buffers: shared hit=2
->
NestedLoop Left Join (cost=2.86..22.94 rows=1 width=8) (actual time=0.099..0.184 rows=2 loops=49)
Buffers:shared hit=1684
->
Nested Loop Left Join (cost=2.43..20.36 rows=1 width=8) (actual time=0.084..0.139 rows=2 loops=49)
Buffers: shared hit=1342
-> Nested Loop Left Join (cost=2.00..17.79 rows=1 width=8) (actual time=0.068..0.106 rows=1 loops=49)
Buffers: shared hit=1064
-> Nested Loop Left Join (cost=1.57..15.22 rows=1 width=8) (actual time=0.053..0.079 rows=1 loops=49)
Buffers: shared hit=818
-> Nested Loop Left Join (cost=1.15..12.64 rows=1 width=8) (actual time=0.038..0.055 rows=1 loops=49)
Buffers: shared hit=588
-> Nested Loop Left Join (cost=0.72..10.07 rows=1 width=8) (actual time=0.023..0.032 rows=1
loops=49)
Buffers: shared hit=366
-> Index Only Scan using record_pkey on record (cost=0.29..7.49 rows=1 width=8) (actual
time=0.005..0.006rows=1 loops=49)
Index Cond: (id = me.record_id)
Heap Fetches: 49
Buffers: shared hit=148
-> Index Scan using intgr_idx_record_id on intgr field100 (cost=0.43..2.56 rows=1 width=8)
(actualtime=0.014..0.017 rows=1 loops=49)
Index Cond: (record_id = record.id)
Filter: (layout_id = 100)
Rows Removed by Filter: 40
Buffers: shared hit=218
-> Index Scan using intgr_idx_record_id on intgr field101 (cost=0.43..2.56 rows=1 width=8)
(actualtime=0.011..0.014 rows=1 loops=50)
Index Cond: (record_id = record.id)
Filter: (layout_id = 101)
Rows Removed by Filter: 41
Buffers: shared hit=222
-> Index Scan using intgr_idx_record_id on intgr field102 (cost=0.43..2.56 rows=1 width=8) (actual
time=0.010..0.014rows=1 loops=52)
Index Cond: (record_id = record.id)
Filter: (layout_id = 102)
Rows Removed by Filter: 42
Buffers: shared hit=230
-> Index Scan using intgr_idx_record_id on intgr field103 (cost=0.43..2.56 rows=1 width=8) (actual
time=0.010..0.015rows=1 loops=56)
Index Cond: (record_id = record.id)
Filter: (layout_id = 103)
Rows Removed by Filter: 45
Buffers: shared hit=246
-> Index Scan using intgr_idx_record_id on intgr field104 (cost=0.43..2.56 rows=1 width=8) (actual
time=0.010..0.016rows=1 loops=64)
Index Cond: (record_id = record.id)
Filter: (layout_id = 104)
Rows Removed by Filter: 49
Buffers: shared hit=278
->
Index Scan using intgr_idx_record_id on intgr field105 (cost=0.43..2.56 rows=1 width=8) (actual time=0.009..0.018
rows=1loops=80)
Index Cond: (record_id = record.id)
Filter: (layout_id = 105)
Rows Removed by Filter: 55
Buffers: shared hit=342
-> Index Scan
usingintgr_idx_record_id on intgr field106 (cost=0.43..2.56 rows=1 width=8) (actual time=0.008..0.020 rows=2
loops=112)
Index
Cond:(record_id = record.id)
Filter:
(layout_id= 106)
Rows
Removedby Filter: 61
Buffers:
sharedhit=470
-> Index Scan using
string_idx_record_idon string field122 (cost=0.42..1.33 rows=1 width=8) (actual time=0.008..0.015 rows=2 loops=176)
Index Cond:
(record_id= record.id)
Filter:
(layout_id= 122)
Rows Removed
byFilter: 15
Buffers:
sharedhit=745
-> Index Scan using
intgr_idx_record_idon intgr field145 (cost=0.43..2.56 rows=1 width=8) (actual time=0.011..0.023 rows=2 loops=304)
Index Cond:
(record_id= record.id)
Filter: (layout_id =
145)
Rows Removed by
Filter:71
Buffers: shared
hit=1238
-> Index Scan using
intgr_idx_record_idon intgr field173 (cost=0.43..2.56 rows=1 width=8) (actual time=0.011..0.023 rows=2 loops=560)
Index Cond: (record_id =
record.id)
Filter: (layout_id = 173)
Rows Removed by Filter: 74
Buffers: shared hit=2262
-> Index Scan using
intgr_idx_record_idon intgr field174 (cost=0.43..2.56 rows=1 width=8) (actual time=0.011..0.023 rows=2 loops=1072)
Index Cond: (record_id =
record.id)
Filter: (layout_id = 174)
Rows Removed by Filter: 75
Buffers: shared hit=4310
-> Index Scan using intgr_idx_record_id on
intgrfield175 (cost=0.43..2.56 rows=1 width=8) (actual time=0.011..0.023 rows=2 loops=2096)
Index Cond: (record_id = record.id)
Filter: (layout_id = 175)
Rows Removed by Filter: 76
Buffers: shared hit=8406
-> Index Scan using intgr_idx_record_id on intgr
field176 (cost=0.43..2.56 rows=1 width=8) (actual time=0.011..0.023 rows=2 loops=4144)
Index Cond: (record_id = record.id)
Filter: (layout_id = 176)
Rows Removed by Filter: 77
Buffers: shared hit=16598
-> Index Scan using intgr_idx_record_id on intgr
field177 (cost=0.43..2.56 rows=1 width=8) (actual time=0.011..0.023 rows=2 loops=8240)
Index Cond: (record_id = record.id)
Filter: (layout_id = 177)
Rows Removed by Filter: 77
Buffers: shared hit=32982
-> Index Scan using intgr_idx_record_id on intgr field178
(cost=0.43..2.56rows=1 width=8) (actual time=0.010..0.023 rows=2 loops=16432)
Index Cond: (record_id = record.id)
Filter: (layout_id = 178)
Rows Removed by Filter: 77
Buffers: shared hit=65750
-> Index Scan using intgr_idx_record_id on intgr field179
(cost=0.43..2.56rows=1 width=8) (actual time=0.010..0.023 rows=2 loops=32816)
Index Cond: (record_id = record.id)
Filter: (layout_id = 179)
Rows Removed by Filter: 77
Buffers: shared hit=131286
-> Index Scan using intgr_idx_record_id on intgr field180
(cost=0.43..2.56rows=1 width=8) (actual time=0.010..0.023 rows=2 loops=65584)
Index Cond: (record_id = record.id)
Filter: (layout_id = 180)
Rows Removed by Filter: 77
Buffers: shared hit=262358
-> Index Scan using intgr_idx_record_id on intgr field181 (cost=0.43..2.56
rows=1width=8) (actual time=0.010..0.023 rows=2 loops=131120)
Index Cond: (record_id = record.id)
Filter: (layout_id = 181)
Rows Removed by Filter: 77
Buffers: shared hit=524502
-> Index Scan using enum_idx_record_id on enum field33 (cost=0.42..1.32 rows=1
width=12)(actual time=0.007..0.010 rows=1 loops=262192)
Index Cond: (record_id = record.id)
Filter: (layout_id = 33)
Rows Removed by Filter: 22
Buffers: shared hit=1048779
-> Index Scan using daterange_idx_record_id on daterange field39 (cost=0.29..0.36 rows=1
width=8)(actual time=0.003..0.006 rows=2 loops=262192)
Index Cond: (record_id = record.id)
Filter: (layout_id = 39)
Buffers: shared hit=786577
-> Index Scan using enum_idx_record_id on enum field41 (cost=0.42..1.32 rows=1 width=12) (actual
time=0.008..0.011rows=2 loops=524336)
Index Cond: (record_id = record.id)
Filter: (layout_id = 41)
Rows Removed by Filter: 21
Buffers: shared hit=2097355
-> Index Scan using enum_idx_record_id on enum field36 (cost=0.42..1.32 rows=1 width=12) (actual
time=0.007..0.009rows=1 loops=1048624)
Index Cond: (record_id = record.id)
Filter: (layout_id = 36)
Rows Removed by Filter: 22
Buffers: shared hit=4194507
-> Index Scan using enum_idx_record_id on enum field42 (cost=0.42..1.32 rows=1 width=8) (actual
time=0.004..0.009rows=1 loops=1048624)
Index Cond: (record_id = record.id)
Filter: (layout_id = 42)
Rows Removed by Filter: 22
Buffers: shared hit=4194507
Planning time: 10.886 ms
Execution time: 62010.404 ms
(325 rows)
SELECT "me"."id"
FROM "current" "me"
left join "record" "record"
ON "record"."id" = "me"."record_id"
left join "intgr" "field100"
ON ( "field100"."layout_id" = 100
AND "field100"."record_id" = "record"."id" )
left join "intgr" "field101"
ON ( "field101"."layout_id" = 101
AND "field101"."record_id" = "record"."id" )
left join "intgr" "field102"
ON ( "field102"."layout_id" = 102
AND "field102"."record_id" = "record"."id" )
left join "intgr" "field103"
ON ( "field103"."layout_id" = 103
AND "field103"."record_id" = "record"."id" )
left join "intgr" "field104"
ON ( "field104"."layout_id" = 104
AND "field104"."record_id" = "record"."id" )
left join "intgr" "field105"
ON ( "field105"."layout_id" = 105
AND "field105"."record_id" = "record"."id" )
left join "intgr" "field106"
ON ( "field106"."layout_id" = 106
AND "field106"."record_id" = "record"."id" )
left join "string" "field122"
ON ( "field122"."layout_id" = 122
AND "field122"."record_id" = "record"."id" )
left join "intgr" "field145"
ON ( "field145"."layout_id" = 145
AND "field145"."record_id" = "record"."id" )
left join "intgr" "field173"
ON ( "field173"."layout_id" = 173
AND "field173"."record_id" = "record"."id" )
left join "intgr" "field174"
ON ( "field174"."layout_id" = 174
AND "field174"."record_id" = "record"."id" )
left join "intgr" "field175"
ON ( "field175"."layout_id" = 175
AND "field175"."record_id" = "record"."id" )
left join "intgr" "field176"
ON ( "field176"."layout_id" = 176
AND "field176"."record_id" = "record"."id" )
left join "intgr" "field177"
ON ( "field177"."layout_id" = 177
AND "field177"."record_id" = "record"."id" )
left join "intgr" "field178"
ON ( "field178"."layout_id" = 178
AND "field178"."record_id" = "record"."id" )
left join "intgr" "field179"
ON ( "field179"."layout_id" = 179
AND "field179"."record_id" = "record"."id" )
left join "intgr" "field180"
ON ( "field180"."layout_id" = 180
AND "field180"."record_id" = "record"."id" )
left join "intgr" "field181"
ON ( "field181"."layout_id" = 181
AND "field181"."record_id" = "record"."id" )
left join "calcval" "field182"
ON ( "field182"."layout_id" = 182
AND "field182"."record_id" = "record"."id" )
left join "enum" "field33"
ON ( "field33"."layout_id" = 33
AND "field33"."record_id" = "record"."id" )
left join "enumval" "value"
ON "value"."id" = "field33"."value"
left join "calcval" "field35"
ON ( "field35"."layout_id" = 35
AND "field35"."record_id" = "record"."id" )
left join "enum" "field36"
ON ( "field36"."layout_id" = 36
AND "field36"."record_id" = "record"."id" )
left join "enumval" "value_2"
ON "value_2"."id" = "field36"."value"
left join "daterange" "field39"
ON ( "field39"."layout_id" = 39
AND "field39"."record_id" = "record"."id" )
left join "enum" "field41"
ON ( "field41"."layout_id" = 41
AND "field41"."record_id" = "record"."id" )
left join "enumval" "value_3"
ON "value_3"."id" = "field41"."value"
left join "enum" "field42"
ON ( "field42"."layout_id" = 42
AND "field42"."record_id" = "record"."id" )
WHERE (( "me"."id" = 3472
OR "me"."id" = 3484
OR "me"."id" = 3510
OR "me"."id" = 3528
OR "me"."id" = 3553
OR "me"."id" = 3561
OR "me"."id" = 3571
OR "me"."id" = 3583
OR "me"."id" = 3591
OR "me"."id" = 3603
OR "me"."id" = 3612
OR "me"."id" = 3631
OR "me"."id" = 3641
OR "me"."id" = 3665
OR "me"."id" = 3671
OR "me"."id" = 3679
OR "me"."id" = 3693
OR "me"."id" = 3700
OR "me"."id" = 3708
OR "me"."id" = 3712
OR "me"."id" = 3742
OR "me"."id" = 3749
OR "me"."id" = 3758
OR "me"."id" = 3768
OR "me"."id" = 3778
OR "me"."id" = 8275
OR "me"."id" = 8312
OR "me"."id" = 8351
OR "me"."id" = 8512
OR "me"."id" = 8532
OR "me"."id" = 8550
OR "me"."id" = 8834
OR "me"."id" = 8859
OR "me"."id" = 14278
OR "me"."id" = 14674
OR "me"."id" = 14675
OR "me"."id" = 14676
OR "me"."id" = 14677
OR "me"."id" = 14678
OR "me"."id" = 14679
OR "me"."id" = 15036
OR "me"."id" = 15037
OR "me"."id" = 15039
OR "me"."id" = 15364
OR "me"."id" = 15395
OR "me"."id" = 15667
OR "me"."id" = 16135
OR "me"."id" = 18430
OR "me"."id" = 18916 ))
ORDER BY "me"."id" ASC;
В списке pgsql-general по дате отправления:
Следующее
От: Andrew BeverleyДата:
Сообщение: Re: Actual rows significantly more than estimated during many joins