Sorry for confusion, it's from attached explain output of the SQL. please check attached. my questions is : for nestloop of two partition tables , they use same partition key and equal join on partition key, the cost could be "outer tables estimated rows" * (average index scan of only one partition of inner table) , instead of "outer tables estimated rows" * (index scans of all partitions), is it possible ? or it's still need running time partition pruning enhancement?
random_page_cost = 1.1, seq_page_cost=1.0,
effective_cache_size=0.75*physical memory size. set random_page_cost=0.9 make optimizer to choose index scan instead of seq scan.
Thanks,
James
David Rowley <dgrowleyml@xxxxxxxxx> 於 2024年7月6日週六 上午8:33寫道：
On Sat, 6 Jul 2024 at 02:43, James Pang <jamespang886@xxxxxxxxx> wrote:
> for nest loop path, since the first one estimated only "8" rows , and they use partitionkeyid as joinkey and all are hash partitions , is it better to estimate cost to 8 (loop times) * 1600 = 12800 (each one loop map to only 1 hash partition bitmap scan ,avg one partition cost), that's much less than 398917.29 of all partitions ?
I'm not really sure where you're getting the numbers from here. The
outer side of the deepest nested loop has an 8 row estimate, not the
nested loop itself. I'm unsure where the 1600 is from. I only see
1669.
As of now, we don't do a great job of costing for partition pruning
that will happen during execution. We won't be inventing anything to
fix that in existing releases of PostgreSQL, so you'll need to either
adjust the code yourself, or find a workaround.
You've not shown us your schema, but perhaps enable_partitionwise_join
= on might help you. Other things that might help are further lowering
random_page_cost or raising effective_cache_size artificially high.
It's hard to tell from here how much random I/O is being costed into
the index scans. You could determine this by checking if the nested
loop plan costs change as a result of doing further increases to
effective_cache_size. You could maybe nudge it up enough for it to win
over the hash join plan. It is possible that this won't work, however.
> for secondary Nest Loop Anti join could be rows 299118 rows * 15.78(avg index scan cost of one partition) = 4,720,082 that still much less than 132168227.57 ?
> for Hash Right join, is it possible to estimate by 8 seq partition scan instead of all 32 hash partitions since the first query estimated 8 rows only ?
> extend statistics may help estimate count(partitionkeyid) based on other columns bind variables, but looks like that did not help table join case.
I can't quite follow this. You'll need to better explain where you're
getting these numbers for me to be able to understand.
David
--SQL text
prepare slowsql10(bigint,bigint,timestamp,timestamp) AS
SELECT m.partitionkeyid, COUNT(w.ctinfo) AS xxxx_yyyy_count
FROM testaa.wmdata m
LEFT JOIN testaa.wmvtee w
ON m.partitionkeyid = w.partitionkeyid
WHERE m.sstype = 'lltype'
AND m.llid = $1
AND m.hhid = $2
AND m.stime BETWEEN $3 AND $4
AND NOT EXISTS (SELECT 1
FROM testaa.wmpct P
WHERE P.partitionkeyid = m.partitionkeyid
AND LOWER(P.ctinfo) = LOWER(w.ctinfo))
GROUP BY m.partitionkeyid
generally speaking, it run in less than 2 seconds, but sometimes, it suddenly go to bad plan.
schema:
all three tables are hash partitioned tables(partitionkeyid column is the top index key too), generally speaking with index scan and partition prunning
with partitionkeyid, it's good plan. but sometimes, optimizer suddenly go to "Hash Right Join" and seq scan on that large table "testaa.wmpct, that made very
bad plan. from the cost, looks like optimizer try to calculate cost for all partitions bitmapindex scan that show huge cost but actually only a small part of
partition needed other (never executed). and similar to estimate the "rows" for leftoutjoin, that made huge difference too.
no idea how to make optimizer to consider partition prunning for this SQL case ?
--the bad plan explain output, with analyze mode, it's hanging there and consumes huge temp space, so cancel that.
test=> explain execute slowsql10(1234567,678901234,'2024-06-15 03:00:00','2024-07-02 03:00:00');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=125657713.20..125659395.82 rows=8 width=62)
Group Key: m.partitionkeyid
-> Sort (cost=125657713.20..125658274.05 rows=224338 width=78)
Sort Key: m.partitionkeyid
-> Hash Anti Join (cost=107542871.02..125637774.81 rows=224338 width=78)
Hash Cond: (((m.partitionkeyid)::text = (p.partitionkeyid)::text) AND (lower((w.ctinfo)::text) = lower((p.ctinfo)::text)))
-> Hash Right Join (cost=22.46..338601.11 rows=299118 width=78)
Hash Cond: ((w.partitionkeyid)::text = (m.partitionkeyid)::text)
-> Append (cost=0.00..307545.17 rows=7477945 width=78)
-> Seq Scan on wmvtee_p0 w.1 (cost=0.00..8263.66 rows=228666 width=78)
-> Seq Scan on wmvtee_p1 w.2 (cost=0.00..9770.55 rows=270255 width=78)
-> Seq Scan on wmvtee_p2 w.3 (cost=0.00..7751.92 rows=214392 width=78)
-> Seq Scan on wmvtee_p3 w.4 (cost=0.00..7760.89 rows=214589 width=78)
-> Seq Scan on wmvtee_p4 w.5 (cost=0.00..8733.78 rows=241678 width=78)
-> Seq Scan on wmvtee_p5 w.6 (cost=0.00..7108.46 rows=196946 width=78)
-> Seq Scan on wmvtee_p6 w.7 (cost=0.00..8047.84 rows=222784 width=78)
-> Seq Scan on wmvtee_p7 w.8 (cost=0.00..6575.69 rows=181869 width=78)
-> Seq Scan on wmvtee_p8 w.9 (cost=0.00..8239.75 rows=227775 width=78)
-> Seq Scan on wmvtee_p9 w.10 (cost=0.00..9270.68 rows=256468 width=78)
-> Seq Scan on wmvtee_p10 w.11 (cost=0.00..10106.13 rows=280213 width=78)
-> Seq Scan on wmvtee_p11 w.12 (cost=0.00..9409.12 rows=259912 width=78)
-> Seq Scan on wmvtee_p12 w.13 (cost=0.00..8599.83 rows=238383 width=78)
-> Seq Scan on wmvtee_p13 w.14 (cost=0.00..8127.63 rows=224763 width=78)
-> Seq Scan on wmvtee_p14 w.15 (cost=0.00..10083.43 rows=279143 width=78)
-> Seq Scan on wmvtee_p15 w.16 (cost=0.00..7526.99 rows=208199 width=78)
-> Seq Scan on wmvtee_p16 w.17 (cost=0.00..6825.91 rows=188791 width=78)
-> Seq Scan on wmvtee_p17 w.18 (cost=0.00..9437.38 rows=261638 width=78)
-> Seq Scan on wmvtee_p18 w.19 (cost=0.00..7069.52 rows=196352 width=78)
-> Seq Scan on wmvtee_p19 w.20 (cost=0.00..8661.49 rows=239449 width=78)
-> Seq Scan on wmvtee_p20 w.21 (cost=0.00..8706.72 rows=240572 width=78)
-> Seq Scan on wmvtee_p21 w.22 (cost=0.00..6967.08 rows=193108 width=78)
-> Seq Scan on wmvtee_p22 w.23 (cost=0.00..10087.26 rows=279626 width=78)
-> Seq Scan on wmvtee_p23 w.24 (cost=0.00..8157.36 rows=225636 width=78)
-> Seq Scan on wmvtee_p24 w.25 (cost=0.00..7144.91 rows=197591 width=78)
-> Seq Scan on wmvtee_p25 w.26 (cost=0.00..9207.82 rows=254682 width=78)
-> Seq Scan on wmvtee_p26 w.27 (cost=0.00..8242.27 rows=228227 width=78)
-> Seq Scan on wmvtee_p27 w.28 (cost=0.00..11231.60 rows=311560 width=78)
-> Seq Scan on wmvtee_p28 w.29 (cost=0.00..9139.16 rows=252716 width=78)
-> Seq Scan on wmvtee_p29 w.30 (cost=0.00..8974.01 rows=249201 width=78)
-> Seq Scan on wmvtee_p30 w.31 (cost=0.00..6467.40 rows=178840 width=78)
-> Seq Scan on wmvtee_p31 w.32 (cost=0.00..8459.21 rows=233921 width=78)
-> Hash (cost=22.36..22.36 rows=8 width=54)
-> Append (cost=0.56..22.36 rows=8 width=54)
-> Index Scan using wmdata_p0_llid_hhid_stime_idx on wmdata_p0 m_1 (co
st=0.56..2.79 rows=1 width=54)
Index Cond: ((llid = '1234567'::bigint) AND (hhid = '678901234'::bigint) AND (stime >= '2024-06-15 03:00:0
0'::timestamp without time zone) AND (stime <= '2024-07-02 03:00:00'::timestamp without time zone))
Filter: ((stime)::text = 'lltype'::text)
-> Index Scan using wmdata_p1_llid_hhid_stime_idx on wmdata_p1 m_2 (co
st=0.56..2.79 rows=1 width=54)
Index Cond: ((llid = '1234567'::bigint) AND (hhid = '678901234'::bigint) AND (stime >= '2024-06-15 03:00:0
0'::timestamp without time zone) AND (stime <= '2024-07-02 03:00:00'::timestamp without time zone))
Filter: ((stime)::text = 'lltype'::text)
-> Index Scan using wmdata_p2_llid_hhid_stime_idx on wmdata_p2 m_3 (co
st=0.56..2.79 rows=1 width=53)
Index Cond: ((llid = '1234567'::bigint) AND (hhid = '678901234'::bigint) AND (stime >= '2024-06-15 03:00:0
0'::timestamp without time zone) AND (stime <= '2024-07-02 03:00:00'::timestamp without time zone))
Filter: ((stime)::text = 'lltype'::text)
-> Index Scan using wmdata_p3_llid_hhid_stime_idx on wmdata_p3 m_4 (co
st=0.56..2.79 rows=1 width=54)
Index Cond: ((llid = '1234567'::bigint) AND (hhid = '678901234'::bigint) AND (stime >= '2024-06-15 03:00:0
0'::timestamp without time zone) AND (stime <= '2024-07-02 03:00:00'::timestamp without time zone))
Filter: ((stime)::text = 'lltype'::text)
-> Index Scan using wmdata_p4_llid_hhid_stime_idx on wmdata_p4 m_5 (co
st=0.56..2.79 rows=1 width=53)
Index Cond: ((llid = '1234567'::bigint) AND (hhid = '678901234'::bigint) AND (stime >= '2024-06-15 03:00:0
0'::timestamp without time zone) AND (stime <= '2024-07-02 03:00:00'::timestamp without time zone))
Filter: ((stime)::text = 'lltype'::text)
-> Index Scan using wmdata_p5_llid_hhid_stime_idx on wmdata_p5 m_6 (co
st=0.56..2.79 rows=1 width=54)
Index Cond: ((llid = '1234567'::bigint) AND (hhid = '678901234'::bigint) AND (stime >= '2024-06-15 03:00:0
0'::timestamp without time zone) AND (stime <= '2024-07-02 03:00:00'::timestamp without time zone))
Filter: ((stime)::text = 'lltype'::text)
-> Index Scan using wmdata_p6_llid_hhid_stime_idx on wmdata_p6 m_7 (co
st=0.56..2.79 rows=1 width=54)
Index Cond: ((llid = '1234567'::bigint) AND (hhid = '678901234'::bigint) AND (stime >= '2024-06-15 03:00:0
0'::timestamp without time zone) AND (stime <= '2024-07-02 03:00:00'::timestamp without time zone))
Filter: ((stime)::text = 'lltype'::text)
-> Index Scan using wmdata_p7_llid_hhid_stime_idx on wmdata_p7 m_8 (co
st=0.56..2.79 rows=1 width=53)
Index Cond: ((llid = '1234567'::bigint) AND (hhid = '678901234'::bigint) AND (stime >= '2024-06-15 03:00:0
0'::timestamp without time zone) AND (stime <= '2024-07-02 03:00:00'::timestamp without time zone))
Filter: ((stime)::text = 'lltype'::text)
-> Hash (cost=70463069.28..70463069.28 rows=1338846752 width=78)
-> Append (cost=0.00..70463069.28 rows=1338846752 width=78)
-> Seq Scan on wmpct_p0 p_1 (cost=0.00..1989539.96 rows=41756896 width=78)
-> Seq Scan on wmpct_p1 p_2 (cost=0.00..1992934.72 rows=41843572 width=78)
-> Seq Scan on wmpct_p2 p_3 (cost=0.00..1993741.76 rows=41856576 width=79)
-> Seq Scan on wmpct_p3 p_4 (cost=0.00..1990916.24 rows=41761624 width=79)
-> Seq Scan on wmpct_p4 p_5 (cost=0.00..1994259.68 rows=41886368 width=79)
-> Seq Scan on wmpct_p5 p_6 (cost=0.00..1988065.64 rows=41698064 width=79)
-> Seq Scan on wmpct_p6 p_7 (cost=0.00..1997608.40 rows=41944940 width=79)
-> Seq Scan on wmpct_p7 p_8 (cost=0.00..1992765.60 rows=41842260 width=78)
-> Seq Scan on wmpct_p8 p_9 (cost=0.00..1990650.68 rows=41792468 width=78)
-> Seq Scan on wmpct_p9 p_10 (cost=0.00..1991570.16 rows=41831616 width=78)
-> Seq Scan on wmpct_p10 p_11 (cost=0.00..1990079.88 rows=41778288 width=78)
-> Seq Scan on wmpct_p11 p_12 (cost=0.00..1998561.68 rows=41954768 width=78)
-> Seq Scan on wmpct_p12 p_13 (cost=0.00..1987778.68 rows=41723568 width=79)
-> Seq Scan on wmpct_p13 p_14 (cost=0.00..1987695.20 rows=41746920 width=78)
-> Seq Scan on wmpct_p14 p_15 (cost=0.00..1993307.48 rows=41838748 width=79)
-> Seq Scan on wmpct_p15 p_16 (cost=0.00..1994292.84 rows=41876484 width=79)
-> Seq Scan on wmpct_p16 p_17 (cost=0.00..1998676.28 rows=41936128 width=78)
-> Seq Scan on wmpct_p17 p_18 (cost=0.00..1988204.08 rows=41742508 width=78)
-> Seq Scan on wmpct_p18 p_19 (cost=0.00..1989231.36 rows=41792836 width=78)
-> Seq Scan on wmpct_p19 p_20 (cost=0.00..1989984.68 rows=41763868 width=79)
-> Seq Scan on wmpct_p20 p_21 (cost=0.00..1992902.00 rows=41849600 width=78)
-> Seq Scan on wmpct_p21 p_22 (cost=0.00..1990993.24 rows=41798624 width=78)
-> Seq Scan on wmpct_p22 p_23 (cost=0.00..1995815.32 rows=41881832 width=77)
-> Seq Scan on wmpct_p23 p_24 (cost=0.00..2003769.68 rows=42071768 width=79)
-> Seq Scan on wmpct_p24 p_25 (cost=0.00..1995707.12 rows=41929512 width=78)
-> Seq Scan on wmpct_p25 p_26 (cost=0.00..1989505.88 rows=41777788 width=78)
-> Seq Scan on wmpct_p26 p_27 (cost=0.00..1993307.60 rows=41897960 width=77)
-> Seq Scan on wmpct_p27 p_28 (cost=0.00..1990527.44 rows=41810544 width=78)
-> Seq Scan on wmpct_p28 p_29 (cost=0.00..1994551.08 rows=41877008 width=78)
-> Seq Scan on wmpct_p29 p_30 (cost=0.00..1995719.52 rows=41945252 width=79)
-> Seq Scan on wmpct_p30 p_31 (cost=0.00..1993073.88 rows=41833388 width=78)
-> Seq Scan on wmpct_p31 p_32 (cost=0.00..1993097.76 rows=41804976 width=78)
(101 rows)
--the good plan,
test=> set enable_seqscan=off;
SET
test=> explain execute slowsql10(1234567,678901234,'2024-06-15 03:00:00','2024-07-02 03:00:00');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
GroupAggregate (cost=132188165.96..132189848.58 rows=8 width=62)
Group Key: m.partitionkeyid
-> Sort (cost=132188165.96..132188726.81 rows=224338 width=78)
Sort Key: m.partitionkeyid
-> Nested Loop Anti Join (cost=40.32..132168227.57 rows=224338 width=78)
Join Filter: (lower((p.ctinfo)::text) = lower((w.ctinfo)::text))
-> Nested Loop Left Join (cost=39.63..398917.29 rows=299118 width=78)
-> Append (cost=0.56..22.36 rows=8 width=54)
-> Index Scan using wmdata_p0_llid_hhid_stime_idx on wmdata_p0 m_1 (cost=0.5
6..2.79 rows=1 width=54)
Index Cond: ((llid = '1234567'::bigint) AND (hhid = '678901234'::bigint) AND (stime >= '2024-06-15 03:00:00'::ti
mestamp without time zone) AND (stime <= '2024-07-02 03:00:00'::timestamp without time zone))
Filter: ((stime)::text = 'lltype'::text)
-> Index Scan using wmdata_p1_llid_hhid_stime_idx on wmdata_p1 m_2 (cost=0.5
6..2.79 rows=1 width=54)
Index Cond: ((llid = '1234567'::bigint) AND (hhid = '678901234'::bigint) AND (stime >= '2024-06-15 03:00:00'::ti
mestamp without time zone) AND (stime <= '2024-07-02 03:00:00'::timestamp without time zone))
Filter: ((stime)::text = 'lltype'::text)
-> Index Scan using wmdata_p2_llid_hhid_stime_idx on wmdata_p2 m_3 (cost=0.5
6..2.79 rows=1 width=53)
Index Cond: ((llid = '1234567'::bigint) AND (hhid = '678901234'::bigint) AND (stime >= '2024-06-15 03:00:00'::ti
mestamp without time zone) AND (stime <= '2024-07-02 03:00:00'::timestamp without time zone))
Filter: ((stime)::text = 'lltype'::text)
-> Index Scan using wmdata_p3_llid_hhid_stime_idx on wmdata_p3 m_4 (cost=0.5
6..2.79 rows=1 width=54)
Index Cond: ((llid = '1234567'::bigint) AND (hhid = '678901234'::bigint) AND (stime >= '2024-06-15 03:00:00'::ti
mestamp without time zone) AND (stime <= '2024-07-02 03:00:00'::timestamp without time zone))
Filter: ((stime)::text = 'lltype'::text)
-> Index Scan using wmdata_p4_llid_hhid_stime_idx on wmdata_p4 m_5 (cost=0.5
6..2.79 rows=1 width=53)
Index Cond: ((llid = '1234567'::bigint) AND (hhid = '678901234'::bigint) AND (stime >= '2024-06-15 03:00:00'::ti
mestamp without time zone) AND (stime <= '2024-07-02 03:00:00'::timestamp without time zone))
Filter: ((stime)::text = 'lltype'::text)
-> Index Scan using wmdata_p5_llid_hhid_stime_idx on wmdata_p5 m_6 (cost=0.5
6..2.79 rows=1 width=54)
Index Cond: ((llid = '1234567'::bigint) AND (hhid = '678901234'::bigint) AND (stime >= '2024-06-15 03:00:00'::ti
mestamp without time zone) AND (stime <= '2024-07-02 03:00:00'::timestamp without time zone))
Filter: ((stime)::text = 'lltype'::text)
-> Index Scan using wmdata_p6_llid_hhid_stime_idx on wmdata_p6 m_7 (cost=0.5
6..2.79 rows=1 width=54)
Index Cond: ((llid = '1234567'::bigint) AND (hhid = '678901234'::bigint) AND (stime >= '2024-06-15 03:00:00'::ti
mestamp without time zone) AND (stime <= '2024-07-02 03:00:00'::timestamp without time zone))
Filter: ((stime)::text = 'lltype'::text)
-> Index Scan using wmdata_p7_llid_hhid_stime_idx on wmdata_p7 m_8 (cost=0.5
6..2.79 rows=1 width=53)
Index Cond: ((llid = '1234567'::bigint) AND (hhid = '678901234'::bigint) AND (stime >= '2024-06-15 03:00:00'::ti
mestamp without time zone) AND (stime <= '2024-07-02 03:00:00'::timestamp without time zone))
Filter: ((stime)::text = 'lltype'::text)
-> Append (cost=39.07..49312.09 rows=54978 width=78)
-> Bitmap Heap Scan on wmvtee_p0 w.1 (cost=39.07..1491.06 rows=1669 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p0_partitionkeyid_intid_idx (cost=0.00..38.65 rows=1669 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p1 w.2 (cost=42.58..1645.42 rows=1838 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p1_partitionkeyid_intid_idx (cost=0.00..42.12 rows=1838 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Index Scan using wmvtee_p2_partitionkeyid_intid_idx on wmvtee_p2 w.3 (cost=0.42.
.1390.39 rows=1588 width=78)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Index Scan using wmvtee_p3_partitionkeyid_intid_idx on wmvtee_p3 w.4 (cost=0.42.
.1501.59 rows=1690 width=78)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p4 w.5 (cost=39.05..1491.76 rows=1667 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p4_partitionkeyid_intid_idx (cost=0.00..38.64 rows=1667 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p5 w.6 (cost=38.71..1459.68 rows=1641 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p5_partitionkeyid_intid_idx (cost=0.00..38.30 rows=1641 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p6 w.7 (cost=39.11..1494.42 rows=1675 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p6_partitionkeyid_intid_idx (cost=0.00..38.70 rows=1675 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p7 w.8 (cost=31.21..1203.09 rows=1347 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p7_partitionkeyid_intid_idx (cost=0.00..30.87 rows=1347 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p8 w.9 (cost=39.41..1527.84 rows=1713 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p8_partitionkeyid_intid_idx (cost=0.00..38.98 rows=1713 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Index Scan using wmvtee_p9_partitionkeyid_intid_idx on wmvtee_p9 w.10 (cost=0.42
..1688.71 rows=1914 width=78)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p10 w.11 (cost=45.59..1753.14 rows=1960 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p10_partitionkeyid_intid_idx (cost=0.00..45.10 rows=1960 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p11 w.12 (cost=40.84..1572.33 rows=1756 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p11_partitionkeyid_intid_idx (cost=0.00..40.40 rows=1756 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p12 w.13 (cost=44.07..1697.56 rows=1907 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p12_partitionkeyid_intid_idx (cost=0.00..43.60 rows=1907 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Index Scan using wmvtee_p13_partitionkeyid_intid_idx on wmvtee_p13 w.14 (cost=0.
42..1610.77 rows=1798 width=78)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p14 w.15 (cost=42.47..1634.85 rows=1824 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p14_partitionkeyid_intid_idx (cost=0.00..42.02 rows=1824 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p15 w.16 (cost=37.64..1450.12 rows=1627 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p15_partitionkeyid_intid_idx (cost=0.00..37.23 rows=1627 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p16 w.17 (cost=36.02..1387.09 rows=1560 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p16_partitionkeyid_intid_idx (cost=0.00..35.63 rows=1560 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p17 w.18 (cost=42.52..1636.81 rows=1830 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p17_partitionkeyid_intid_idx (cost=0.00..42.06 rows=1830 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Index Scan using wmvtee_p18_partitionkeyid_intid_idx on wmvtee_p18 w.19 (cost=0.
42..1380.01 rows=1558 width=78)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p19 w.20 (cost=39.11..1497.83 rows=1674 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p19_partitionkeyid_intid_idx (cost=0.00..38.69 rows=1674 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p20 w.21 (cost=42.54..1649.68 rows=1851 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p20_partitionkeyid_intid_idx (cost=0.00..42.08 rows=1851 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p21 w.22 (cost=37.36..1430.64 rows=1609 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p21_partitionkeyid_intid_idx (cost=0.00..36.96 rows=1609 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p22 w.23 (cost=44.07..1691.94 rows=1889 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p22_partitionkeyid_intid_idx (cost=0.00..43.60 rows=1889 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p23 w.24 (cost=39.08..1491.71 rows=1671 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p23_partitionkeyid_intid_idx (cost=0.00..38.67 rows=1671 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p24 w.25 (cost=35.81..1365.41 rows=1532 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p24_partitionkeyid_intid_idx (cost=0.00..35.42 rows=1532 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p25 w.26 (cost=39.47..1540.92 rows=1721 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p25_partitionkeyid_intid_idx (cost=0.00..39.04 rows=1721 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p26 w.27 (cost=40.49..1542.47 rows=1729 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p26_partitionkeyid_intid_idx (cost=0.00..40.06 rows=1729 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p27 w.28 (cost=44.52..1746.45 rows=1947 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p27_partitionkeyid_intid_idx (cost=0.00..44.04 rows=1947 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p28 w.29 (cost=42.52..1635.74 rows=1831 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p28_partitionkeyid_intid_idx (cost=0.00..42.07 rows=1831 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p29 w.30 (cost=39.01..1488.32 rows=1661 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p29_partitionkeyid_intid_idx (cost=0.00..38.59 rows=1661 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p30 w.31 (cost=35.88..1369.64 rows=1542 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p30_partitionkeyid_intid_idx (cost=0.00..35.50 rows=1542 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p31 w.32 (cost=40.86..1569.81 rows=1759 width=78)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p31_partitionkeyid_intid_idx (cost=0.00..40.43 rows=1759 width=0)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Append (cost=0.69..516.96 rows=4010 width=78)
-> Index Only Scan using wmpct_p0_partitionkeyid_ctinfo_idx on wmpct_p0 p_1 (cost=0.
69..15.78 rows=124 width=78)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p1_partitionkeyid_ctinfo_idx on wmpct_p1 p_2 (cost=0.
69..16.54 rows=128 width=78)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p2_partitionkeyid_ctinfo_idx on wmpct_p2 p_3 (cost=0.
69..14.67 rows=123 width=79)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p3_partitionkeyid_ctinfo_idx on wmpct_p3 p_4 (cost=0.
69..15.94 rows=125 width=79)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p4_partitionkeyid_ctinfo_idx on wmpct_p4 p_5 (cost=0.
69..16.39 rows=127 width=79)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p5_partitionkeyid_ctinfo_idx on wmpct_p5 p_6 (cost=0.
69..15.80 rows=125 width=79)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p6_partitionkeyid_ctinfo_idx on wmpct_p6 p_7 (cost=0.
69..17.23 rows=136 width=79)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p7_partitionkeyid_ctinfo_idx on wmpct_p7 p_8 (cost=0.
69..16.52 rows=127 width=78)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p8_partitionkeyid_ctinfo_idx on wmpct_p8 p_9 (cost=0.
69..15.92 rows=124 width=78)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p9_partitionkeyid_ctinfo_idx on wmpct_p9 p_10 (cost=0
.69..17.09 rows=128 width=78)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p10_partitionkeyid_ctinfo_idx on wmpct_p10 p_11 (cost
=0.69..15.73 rows=121 width=78)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p11_partitionkeyid_ctinfo_idx on wmpct_p11 p_12 (cost
=0.69..17.32 rows=133 width=78)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p12_partitionkeyid_ctinfo_idx on wmpct_p12 p_13 (cost
=0.69..15.61 rows=122 width=79)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p13_partitionkeyid_ctinfo_idx on wmpct_p13 p_14 (cost
=0.69..12.67 rows=119 width=78)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p14_partitionkeyid_ctinfo_idx on wmpct_p14 p_15 (cost
=0.69..12.57 rows=121 width=79)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p15_partitionkeyid_ctinfo_idx on wmpct_p15 p_16 (cost
=0.69..15.73 rows=129 width=79)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p16_partitionkeyid_ctinfo_idx on wmpct_p16 p_17 (cost
=0.69..15.84 rows=127 width=78)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p17_partitionkeyid_ctinfo_idx on wmpct_p17 p_18 (cost
=0.69..16.57 rows=122 width=78)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p18_partitionkeyid_ctinfo_idx on wmpct_p18 p_19 (cost
=0.69..16.18 rows=123 width=78)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p19_partitionkeyid_ctinfo_idx on wmpct_p19 p_20 (cost
=0.69..15.99 rows=120 width=79)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p20_partitionkeyid_ctinfo_idx on wmpct_p20 p_21 (cost
=0.69..12.79 rows=126 width=78)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p21_partitionkeyid_ctinfo_idx on wmpct_p21 p_22 (cost
=0.69..16.28 rows=129 width=78)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p22_partitionkeyid_ctinfo_idx on wmpct_p22 p_23 (cost
=0.69..15.90 rows=123 width=77)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p23_partitionkeyid_ctinfo_idx on wmpct_p23 p_24 (cost
=0.69..17.16 rows=132 width=79)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p24_partitionkeyid_ctinfo_idx on wmpct_p24 p_25 (cost
=0.69..15.83 rows=119 width=78)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p25_partitionkeyid_ctinfo_idx on wmpct_p25 p_26 (cost
=0.69..12.97 rows=128 width=78)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p26_partitionkeyid_ctinfo_idx on wmpct_p26 p_27 (cost
=0.69..16.02 rows=122 width=77)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p27_partitionkeyid_ctinfo_idx on wmpct_p27 p_28 (cost
=0.69..15.75 rows=122 width=78)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p28_partitionkeyid_ctinfo_idx on wmpct_p28 p_29 (cost
=0.69..13.79 rows=128 width=78)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p29_partitionkeyid_ctinfo_idx on wmpct_p29 p_30 (cost
=0.69..13.36 rows=127 width=79)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p30_partitionkeyid_ctinfo_idx on wmpct_p30 p_31 (cost
=0.69..14.91 rows=129 width=78)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
-> Index Only Scan using wmpct_p31_partitionkeyid_ctinfo_idx on wmpct_p31 p_32 (cost
=0.69..16.01 rows=121 width=78)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
(216 rows)
--the good plan with explain analyze output
set enable_seqscan=off;
explain (analyze) execute slowsql10(1234567,678901234,'2024-06-15 03:00:00','2024-07-02 03:00:00');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
GroupAggregate (cost=114352867.34..114353991.99 rows=8 width=62) (actual time=23.000..23.018 rows=10 loops=1)
Group Key: m.partitionkeyid
-> Sort (cost=114352867.34..114353242.20 rows=149942 width=78) (actual time=22.995..23.010 rows=10 loops=1)
Sort Key: m.partitionkeyid
Sort Method: quicksort Memory: 26kB
-> Nested Loop Anti Join (cost=39.03..114339976.79 rows=149942 width=78) (actual time=0.178..22.975 rows=10 loops=1)
Join Filter: (lower((p.ctinfo)::text) = lower((w.ctinfo)::text))
Rows Removed by Join Filter: 1074
-> Nested Loop Left Join (cost=38.34..395099.49 rows=299885 width=78) (actual time=0.047..6.781 rows=10 loops=1)
-> Append (cost=0.56..22.36 rows=8 width=54) (actual time=0.022..0.188 rows=10 loops=1)
-> Index Scan using wmdata_p0_llid_hhid_stime_idx on wmdata_p0 m_1 (cost=0.5
6..2.79 rows=1 width=54) (actual time=0.022..0.023 rows=1 loops=1)
Index Cond: ((llid = '1234567'::bigint) AND (hhid = '678901234'::bigint) AND (stime >= '2024-06-15 03:00:00'::tim
estamp without time zone) AND (stime <= '2024-07-02 03:00:00'::timestamp without time zone))
Filter: ((sstype)::text = 'lltype'::text)
-> Index Scan using wmdata_p1_llid_hhid_stime_idx on wmdata_p1 m_2 (cost=0.5
6..2.79 rows=1 width=53) (actual time=0.018..0.018 rows=0 loops=1)
Index Cond: ((llid = '1234567'::bigint) AND (hhid = '678901234'::bigint) AND (stime >= '2024-06-15 03:00:00'::tim
estamp without time zone) AND (stime <= '2024-07-02 03:00:00'::timestamp without time zone))
Filter: ((sstype)::text = 'lltype'::text)
-> Index Scan using wmdata_p2_llid_hhid_stime_idx on wmdata_p2 m_3 (cost=0.5
6..2.79 rows=1 width=53) (actual time=0.015..0.017 rows=1 loops=1)
Index Cond: ((llid = '1234567'::bigint) AND (hhid = '678901234'::bigint) AND (stime >= '2024-06-15 03:00:00'::tim
estamp without time zone) AND (stime <= '2024-07-02 03:00:00'::timestamp without time zone))
Filter: ((sstype)::text = 'lltype'::text)
-> Index Scan using wmdata_p3_llid_hhid_stime_idx on wmdata_p3 m_4 (cost=0.5
6..2.79 rows=1 width=53) (actual time=0.020..0.020 rows=1 loops=1)
Index Cond: ((llid = '1234567'::bigint) AND (hhid = '678901234'::bigint) AND (stime >= '2024-06-15 03:00:00'::tim
estamp without time zone) AND (stime <= '2024-07-02 03:00:00'::timestamp without time zone))
Filter: ((sstype)::text = 'lltype'::text)
-> Index Scan using wmdata_p4_llid_hhid_stime_idx on wmdata_p4 m_5 (cost=0.5
6..2.79 rows=1 width=54) (actual time=0.019..0.020 rows=1 loops=1)
Index Cond: ((llid = '1234567'::bigint) AND (hhid = '678901234'::bigint) AND (stime >= '2024-06-15 03:00:00'::tim
estamp without time zone) AND (stime <= '2024-07-02 03:00:00'::timestamp without time zone))
Filter: ((sstype)::text = 'lltype'::text)
-> Index Scan using wmdata_p5_llid_hhid_stime_idx on wmdata_p5 m_6 (cost=0.5
6..2.79 rows=1 width=54) (actual time=0.027..0.032 rows=2 loops=1)
Index Cond: ((llid = '1234567'::bigint) AND (hhid = '678901234'::bigint) AND (stime >= '2024-06-15 03:00:00'::tim
estamp without time zone) AND (stime <= '2024-07-02 03:00:00'::timestamp without time zone))
Filter: ((sstype)::text = 'lltype'::text)
-> Index Scan using wmdata_p6_llid_hhid_stime_idx on wmdata_p6 m_7 (cost=0.5
6..2.79 rows=1 width=54) (actual time=0.022..0.023 rows=1 loops=1)
Index Cond: ((llid = '1234567'::bigint) AND (hhid = '678901234'::bigint) AND (stime >= '2024-06-15 03:00:00'::tim
estamp without time zone) AND (stime <= '2024-07-02 03:00:00'::timestamp without time zone))
Filter: ((sstype)::text = 'lltype'::text)
-> Index Scan using wmdata_p7_llid_hhid_stime_idx on wmdata_p7 m_8 (cost=0.5
6..2.79 rows=1 width=54) (actual time=0.026..0.031 rows=3 loops=1)
Index Cond: ((llid = '1234567'::bigint) AND (hhid = '678901234'::bigint) AND (stime >= '2024-06-15 03:00:00'::tim
estamp without time zone) AND (stime <= '2024-07-02 03:00:00'::timestamp without time zone))
Filter: ((sstype)::text = 'lltype'::text)
-> Append (cost=37.78..48840.35 rows=54429 width=78) (actual time=0.654..0.655 rows=0 loops=10)
-> Bitmap Heap Scan on wmvtee_p0 w_1 (cost=37.78..1469.93 rows=1645 width=78) (never executed)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p0_partitionkeyid_intid_idx (cost=0.00..37.37 rows=1645 width=0)
(never executed)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p1 w_2 (cost=41.20..1613.69 rows=1802 width=78) (never executed)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p1_partitionkeyid_intid_idx (cost=0.00..40.75 rows=1802 width=0)
(never executed)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Index Scan using wmvtee_p2_partitionkeyid_intid_idx on wmvtee_p2 w_3 (cost=0.42.
.1456.42 rows=1675 width=78) (never executed)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Index Scan using wmvtee_p3_partitionkeyid_intid_idx on wmvtee_p3 w_4 (cost=0.42.
.1492.25 rows=1676 width=78) (never executed)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p4 w_5 (cost=37.74..1452.77 rows=1622 width=78) (never executed)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p4_partitionkeyid_intid_idx (cost=0.00..37.34 rows=1622 width=0)
(never executed)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p5 w_6 (cost=37.54..1435.65 rows=1614 width=78) (never executed)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p5_partitionkeyid_intid_idx (cost=0.00..37.14 rows=1614 width=0)
(never executed)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p6 w_7 (cost=37.28..1413.24 rows=1580 width=78) (never executed)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p6_partitionkeyid_intid_idx (cost=0.00..36.88 rows=1580 width=0)
(never executed)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p7 w_8 (cost=31.29..1210.50 rows=1357 width=78) (actual time=0.007..0.007 rows=
0 loops=2)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p7_partitionkeyid_intid_idx (cost=0.00..30.95 rows=1357 width=0)
(actual time=0.006..0.006 rows=0 loops=2)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p8 w_9 (cost=40.65..1545.58 rows=1731 width=78) (actual time=0.012..0.013 rows=
0 loops=1)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p8_partitionkeyid_intid_idx (cost=0.00..40.22 rows=1731 width=0)
(actual time=0.009..0.009 rows=0 loops=1)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Index Scan using wmvtee_p9_partitionkeyid_intid_idx on wmvtee_p9 w_10 (cost=0.42
..1599.63 rows=1819 width=78) (never executed)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p10 w_11 (cost=45.69..1763.70 rows=1973 width=78) (never executed)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p10_partitionkeyid_intid_idx (cost=0.00..45.20 rows=1973 width=0)
(never executed)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p11 w_12 (cost=41.03..1593.43 rows=1780 width=78) (never executed)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p11_partitionkeyid_intid_idx (cost=0.00..40.58 rows=1780 width=0)
(never executed)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p12 w_13 (cost=42.58..1654.12 rows=1856 width=78) (never executed)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p12_partitionkeyid_intid_idx (cost=0.00..42.12 rows=1856 width=0)
(never executed)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Index Scan using wmvtee_p13_partitionkeyid_intid_idx on wmvtee_p13 w_14 (cost=0.
42..1576.98 rows=1756 width=78) (never executed)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p14 w_15 (cost=42.56..1645.42 rows=1836 width=78) (never executed)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p14_partitionkeyid_intid_idx (cost=0.00..42.11 rows=1836 width=0)
(never executed)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p15 w_16 (cost=37.66..1452.56 rows=1630 width=78) (actual time=0.011..0.011 row
s=0 loops=1)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p15_partitionkeyid_intid_idx (cost=0.00..37.26 rows=1630 width=0)
(actual time=0.007..0.007 rows=0 loops=1)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Index Scan using wmvtee_p16_partitionkeyid_intid_idx on wmvtee_p16 w_17 (cost=0.
42..1388.62 rows=1547 width=78) (never executed)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p17 w_18 (cost=42.20..1602.16 rows=1789 width=78) (never executed)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p17_partitionkeyid_intid_idx (cost=0.00..41.75 rows=1789 width=0)
(never executed)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Index Scan using wmvtee_p18_partitionkeyid_intid_idx on wmvtee_p18 w_19 (cost=0.
42..1403.38 rows=1583 width=78) (actual time=0.693..0.693 rows=0 loops=1)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p19 w_20 (cost=39.29..1517.88 rows=1698 width=78) (never executed)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p19_partitionkeyid_intid_idx (cost=0.00..38.87 rows=1698 width=0)
(never executed)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p20 w_21 (cost=42.32..1626.52 rows=1823 width=78) (never executed)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p20_partitionkeyid_intid_idx (cost=0.00..41.87 rows=1823 width=0)
(never executed)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p21 w_22 (cost=37.36..1430.92 rows=1609 width=78) (actual time=1.455..1.455 row
s=0 loops=1)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p21_partitionkeyid_intid_idx (cost=0.00..36.96 rows=1609 width=0)
(actual time=1.453..1.453 rows=0 loops=1)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p22 w_23 (cost=42.69..1659.15 rows=1852 width=78) (actual time=1.501..1.501 row
s=0 loops=1)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p22_partitionkeyid_intid_idx (cost=0.00..42.23 rows=1852 width=0)
(actual time=1.498..1.498 rows=0 loops=1)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p23 w_24 (cost=40.65..1558.96 rows=1749 width=78) (never executed)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p23_partitionkeyid_intid_idx (cost=0.00..40.21 rows=1749 width=0)
(never executed)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p24 w_25 (cost=34.32..1307.60 rows=1464 width=78) (never executed)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p24_partitionkeyid_intid_idx (cost=0.00..33.95 rows=1464 width=0)
(never executed)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p25 w_26 (cost=39.30..1521.93 rows=1699 width=78) (never executed)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p25_partitionkeyid_intid_idx (cost=0.00..38.88 rows=1699 width=0)
(never executed)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p26 w_27 (cost=39.11..1495.34 rows=1674 width=78) (never executed)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p26_partitionkeyid_intid_idx (cost=0.00..38.69 rows=1674 width=0)
(never executed)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p27 w_28 (cost=44.25..1715.74 rows=1911 width=78) (actual time=0.008..0.008 row
s=0 loops=1)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p27_partitionkeyid_intid_idx (cost=0.00..43.77 rows=1911 width=0)
(actual time=0.006..0.007 rows=0 loops=1)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p28 w_29 (cost=42.63..1647.33 rows=1845 width=78) (actual time=1.435..1.435 row
s=0 loops=1)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p28_partitionkeyid_intid_idx (cost=0.00..42.17 rows=1845 width=0)
(actual time=1.429..1.429 rows=0 loops=1)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p29 w_30 (cost=38.87..1472.75 rows=1643 width=78) (actual time=1.385..1.386 row
s=0 loops=1)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p29_partitionkeyid_intid_idx (cost=0.00..38.46 rows=1643 width=0)
(actual time=1.383..1.383 rows=0 loops=1)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p30 w_31 (cost=32.87..1263.72 rows=1419 width=78) (never executed)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p30_partitionkeyid_intid_idx (cost=0.00..32.51 rows=1419 width=0)
(never executed)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Bitmap Heap Scan on wmvtee_p31 w_32 (cost=40.97..1580.35 rows=1772 width=78) (never executed)
Recheck Cond: ((m.partitionkeyid)::text = (partitionkeyid)::text)
-> Bitmap Index Scan on wmvtee_p31_partitionkeyid_intid_idx (cost=0.00..40.52 rows=1772 width=0)
(never executed)
Index Cond: ((partitionkeyid)::text = (m.partitionkeyid)::text)
-> Append (cost=0.69..595.10 rows=4018 width=78) (actual time=0.606..1.595 rows=107 loops=10)
-> Index Only Scan using wmpct_p0_partitionkeyid_ctinfo_idx on wmpct_p0 p_1 (cost=0.
69..18.72 rows=127 width=78) (never executed)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
-> Index Only Scan using wmpct_p1_partitionkeyid_ctinfo_idx on wmpct_p1 p_2 (cost=0.
69..18.57 rows=126 width=78) (never executed)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
-> Index Only Scan using wmpct_p2_partitionkeyid_ctinfo_idx on wmpct_p2 p_3 (cost=0.
69..18.66 rows=131 width=77) (never executed)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
-> Index Only Scan using wmpct_p3_partitionkeyid_ctinfo_idx on wmpct_p3 p_4 (cost=0.
69..18.26 rows=124 width=78) (never executed)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
-> Index Only Scan using wmpct_p4_partitionkeyid_ctinfo_idx on wmpct_p4 p_5 (cost=0.
69..17.98 rows=124 width=79) (never executed)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
-> Index Only Scan using wmpct_p5_partitionkeyid_ctinfo_idx on wmpct_p5 p_6 (cost=0.
69..18.55 rows=125 width=79) (never executed)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
-> Index Only Scan using wmpct_p6_partitionkeyid_ctinfo_idx on wmpct_p6 p_7 (cost=0.
69..18.57 rows=126 width=78) (never executed)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
-> Index Only Scan using wmpct_p7_partitionkeyid_ctinfo_idx on wmpct_p7 p_8 (cost=0.
69..17.47 rows=118 width=78) (actual time=0.026..0.045 rows=108 loops=2)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 1
-> Index Only Scan using wmpct_p8_partitionkeyid_ctinfo_idx on wmpct_p8 p_9 (cost=0.
69..19.86 rows=137 width=78) (actual time=0.051..0.083 rows=125 loops=1)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 1
-> Index Only Scan using wmpct_p9_partitionkeyid_ctinfo_idx on wmpct_p9 p_10 (cost=0
.69..18.45 rows=127 width=79) (never executed)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
-> Index Only Scan using wmpct_p10_partitionkeyid_ctinfo_idx on wmpct_p10 p_11 (cost
=0.69..18.59 rows=127 width=78) (never executed)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
-> Index Only Scan using wmpct_p11_partitionkeyid_ctinfo_idx on wmpct_p11 p_12 (cost
=0.69..19.71 rows=136 width=78) (never executed)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
-> Index Only Scan using wmpct_p12_partitionkeyid_ctinfo_idx on wmpct_p12 p_13 (cost
=0.69..17.54 rows=122 width=78) (never executed)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
-> Index Only Scan using wmpct_p13_partitionkeyid_ctinfo_idx on wmpct_p13 p_14 (cost
=0.69..16.01 rows=121 width=78) (never executed)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
-> Index Only Scan using wmpct_p14_partitionkeyid_ctinfo_idx on wmpct_p14 p_15 (cost
=0.69..16.75 rows=124 width=79) (never executed)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
-> Index Only Scan using wmpct_p15_partitionkeyid_ctinfo_idx on wmpct_p15 p_16 (cost
=0.69..17.71 rows=124 width=78) (actual time=0.049..0.087 rows=232 loops=1)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 1
-> Index Only Scan using wmpct_p16_partitionkeyid_ctinfo_idx on wmpct_p16 p_17 (cost
=0.69..17.60 rows=118 width=78) (never executed)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
-> Index Only Scan using wmpct_p17_partitionkeyid_ctinfo_idx on wmpct_p17 p_18 (cost
=0.69..18.81 rows=124 width=77) (never executed)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
-> Index Only Scan using wmpct_p18_partitionkeyid_ctinfo_idx on wmpct_p18 p_19 (cost
=0.69..19.14 rows=127 width=78) (actual time=1.269..1.270 rows=6 loops=1)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
-> Index Only Scan using wmpct_p19_partitionkeyid_ctinfo_idx on wmpct_p19 p_20 (cost
=0.69..18.72 rows=127 width=78) (never executed)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
-> Index Only Scan using wmpct_p20_partitionkeyid_ctinfo_idx on wmpct_p20 p_21 (cost
=0.69..13.60 rows=125 width=79) (never executed)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
-> Index Only Scan using wmpct_p21_partitionkeyid_ctinfo_idx on wmpct_p21 p_22 (cost
=0.69..18.78 rows=130 width=78) (actual time=1.555..5.706 rows=67 loops=1)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 6
-> Index Only Scan using wmpct_p22_partitionkeyid_ctinfo_idx on wmpct_p22 p_23 (cost
=0.69..17.48 rows=119 width=78) (actual time=0.693..1.333 rows=38 loops=1)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 1
-> Index Only Scan using wmpct_p23_partitionkeyid_ctinfo_idx on wmpct_p23 p_24 (cost
=0.69..18.33 rows=128 width=78) (never executed)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
-> Index Only Scan using wmpct_p24_partitionkeyid_ctinfo_idx on wmpct_p24 p_25 (cost
=0.69..18.45 rows=127 width=78) (never executed)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
-> Index Only Scan using wmpct_p25_partitionkeyid_ctinfo_idx on wmpct_p25 p_26 (cost
=0.69..15.85 rows=120 width=79) (never executed)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
-> Index Only Scan using wmpct_p26_partitionkeyid_ctinfo_idx on wmpct_p26 p_27 (cost
=0.69..18.81 rows=132 width=78) (never executed)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
-> Index Only Scan using wmpct_p27_partitionkeyid_ctinfo_idx on wmpct_p27 p_28 (cost
=0.69..18.19 rows=120 width=78) (actual time=0.044..0.058 rows=111 loops=1)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
-> Index Only Scan using wmpct_p28_partitionkeyid_ctinfo_idx on wmpct_p28 p_29 (cost
=0.69..17.85 rows=132 width=78) (actual time=1.599..3.695 rows=21 loops=1)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 5
-> Index Only Scan using wmpct_p29_partitionkeyid_ctinfo_idx on wmpct_p29 p_30 (cost
=0.69..16.33 rows=124 width=79) (actual time=0.726..3.540 rows=257 loops=1)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
-> Index Only Scan using wmpct_p30_partitionkeyid_ctinfo_idx on wmpct_p30 p_31 (cost
=0.69..17.24 rows=121 width=78) (never executed)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
-> Index Only Scan using wmpct_p31_partitionkeyid_ctinfo_idx on wmpct_p31 p_32 (cost
=0.69..18.41 rows=125 width=78) (never executed)
Index Cond: (partitionkeyid = (m.partitionkeyid)::text)
Heap Fetches: 0
Planning Time: 5.394 ms
Execution Time: 23.929 ms
(250 rows)
