gpdb
gpdb copied to clipboard
greenplum-db
Pivotal Optimizer (GPORCA) failed to produce plan (unexpected)
Bug Report
Pivotal Optimizer (GPORCA) failed to produce plan (unexpected)
Greenplum version or build
6.17.2
OS version and uname -a
gp@dwmaster:/data/gp/master/gpseg-1/pg_log$ uname -a Linux dwmaster 5.4.0-48-generic #52~18.04.1-Ubuntu SMP Thu Sep 10 12:50:22 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
autoconf options used ( config.status --config )
Installation information ( pg_config )
Expected behavior
use GPORCA to exec query from Multi partition tables join
Actual behavior
roll back to Postgres query optimizer , and log file shown "Pivotal Optimizer (GPORCA) failed to produce plan (unexpected)"", but no reason
Step to reproduce the behavior
only found log like this:
2022-07-20 00:00:05.248550 PDT,"fenxi","demo",p18782,th-1126980032,"10.19.134.27","32525",2022-07-19 23:31:49 PDT,0,con1827,cmd80,seg-1,,,,,"LOG","00000","context: 1, 3072, 1952, 3072, 0, CacheMemoryContext/pg_user_mapping_user_server_index ",,,,,,,0,,,, 2022-07-20 00:00:05.248578 PDT,"fenxi","demo",p18782,th-1126980032,"10.19.134.27","32525",2022-07-19 23:31:49 PDT,0,con1827,cmd80,seg-1,,,,,"LOG","00000","context: 1, 1024, 120, 1024, 0, CacheMemoryContext/pg_partition_rule_oid_index ",,,,,,,0,,,, 2022-07-20 00:00:05.248605 PDT,"fenxi","demo",p18782,th-1126980032,"10.19.134.27","32525",2022-07-19 23:31:49 PDT,0,con1827,cmd80,seg-1,,,,,"LOG","00000","context: 1, 1024, 120, 1024, 0, CacheMemoryContext/pg_extprotocol_oid_index ",,,,,,,0,,,, 2022-07-20 00:00:05.248632 PDT,"fenxi","demo",p18782,th-1126980032,"10.19.134.27","32525",2022-07-19 23:31:49 PDT,0,con1827,cmd80,seg-1,,,,,"LOG","00000","context: 1, 3072, 1952, 3072, 0, CacheMemoryContext/pg_conversion_name_nsp_index ",,,,,,,0,,,, 2022-07-20 00:00:05.248667 PDT,"fenxi","demo",p18782,th-1126980032,"10.19.134.27","32525",2022-07-19 23:31:49 PDT,0,con1827,cmd80,seg-1,,,,,"LOG","00000","context: 1, 1024, 72, 1024, 0, CacheMemoryContext/pg_authid_oid_index ",,,,,,,0,,,, 2022-07-20 00:00:05.248693 PDT,"fenxi","demo",p18782,th-1126980032,"10.19.134.27","32525",2022-07-19 23:31:49 PDT,0,con1827,cmd80,seg-1,,,,,"LOG","00000","context: 1, 3072, 1952, 3072, 0, CacheMemoryContext/pg_auth_members_member_role_index ",,,,,,,0,,,, 2022-07-20 00:00:05.248721 PDT,"fenxi","demo",p18782,th-1126980032,"10.19.134.27","32525",2022-07-19 23:31:49 PDT,0,con1827,cmd80,seg-1,,,,,"LOG","00000","context: 1, 3072, 1976, 3072, 0, CacheMemoryContext/pg_tablespace_oid_index ",,,,,,,0,,,, 2022-07-20 00:00:05.248748 PDT,"fenxi","demo",p18782,th-1126980032,"10.19.134.27","32525",2022-07-19 23:31:49 PDT,0,con1827,cmd80,seg-1,,,,,"LOG","00000","context: 1, 1024, 120, 1024, 0, CacheMemoryContext/pg_resgroup_rsgname_index ",,,,,,,0,,,, 2022-07-20 00:00:05.248775 PDT,"fenxi","demo",p18782,th-1126980032,"10.19.134.27","32525",2022-07-19 23:31:49 PDT,0,con1827,cmd80,seg-1,,,,,"LOG","00000","context: 1, 1024, 72, 1024, 0, CacheMemoryContext/pg_database_datname_index ",,,,,,,0,,,, 2022-07-20 00:00:05.248804 PDT,"fenxi","demo",p18782,th-1126980032,"10.19.134.27","32525",2022-07-19 23:31:49 PDT,0,con1827,cmd80,seg-1,,,,,"LOG","00000","context: 1, 3072, 1952, 3072, 0, CacheMemoryContext/pg_auth_members_role_member_index ",,,,,,,0,,,, 2022-07-20 00:00:05.248845 PDT,"fenxi","demo",p18782,th-1126980032,"10.19.134.27","32525",2022-07-19 23:31:49 PDT,0,con1827,cmd80,seg-1,,,,,"LOG","00000","context: 1, 1024, 72, 1024, 0, CacheMemoryContext/pg_database_oid_index ",,,,,,,0,,,, 2022-07-20 00:00:05.248872 PDT,"fenxi","demo",p18782,th-1126980032,"10.19.134.27","32525",2022-07-19 23:31:49 PDT,0,con1827,cmd80,seg-1,,,,,"LOG","00000","context: 1, 1024, 72, 1024, 0, CacheMemoryContext/pg_authid_rolname_index ",,,,,,,0,,,, 2022-07-20 00:00:05.248899 PDT,"fenxi","demo",p18782,th-1126980032,"10.19.134.27","32525",2022-07-19 23:31:49 PDT,0,con1827,cmd80,seg-1,,,,,"LOG","00000","context: 1, 1024, 120, 1024, 0, CacheMemoryContext/pg_resgroup_oid_index ",,,,,,,0,,,, 2022-07-20 00:00:05.248926 PDT,"fenxi","demo",p18782,th-1126980032,"10.19.134.27","32525",2022-07-19 23:31:49 PDT,0,con1827,cmd80,seg-1,,,,,"LOG","00000","context: 1, 1024, 120, 1024, 0, CacheMemoryContext/pg_auth_time_constraint_authid_index ",,,,,,,0,,,, 2022-07-20 00:00:05.248954 PDT,"fenxi","demo",p18782,th-1126980032,"10.19.134.27","32525",2022-07-19 23:31:49 PDT,0,con1827,cmd80,seg-1,,,,,"LOG","00000","context: 1, 68519992, 0, 127806600, 59286608, GPORCA Top-level Memory Context ",,,,,,,0,,,, 2022-07-20 00:00:05.248982 PDT,"fenxi","demo",p18782,th-1126980032,"10.19.134.27","32525",2022-07-19 23:31:49 PDT,0,con1827,cmd80,seg-1,,,,,"LOG","00000","context: 26, 6712952, 4494848, 10304672, 3591720, GPORCA Top-level Memory Context/GPORCA memory pool ",,,,,,,0,,,, 2022-07-20 00:00:05.249010 PDT,"fenxi","demo",p18782,th-1126980032,"10.19.134.27","32525",2022-07-19 23:31:49 PDT,0,con1827,cmd80,seg-1,,,,,"LOG","00000","context: 1, 24576, 928, 24576, 0, MdSmgr ",,,,,,,0,,,, 2022-07-20 00:00:05.249035 PDT,"fenxi","demo",p18782,th-1126980032,"10.19.134.27","32525",2022-07-19 23:31:49 PDT,0,con1827,cmd80,seg-1,,,,,"LOG","00000","context: 1, 1040384, 99456, 1040384, 0, LOCALLOCK hash ",,,,,,,0,,,, 2022-07-20 00:00:05.249061 PDT,"fenxi","demo",p18782,th-1126980032,"10.19.134.27","32525",2022-07-19 23:31:49 PDT,0,con1827,cmd80,seg-1,,,,,"LOG","00000","context: 1, 8192, 8160, 8192, 0, QueryInfoCollectorMemCtxt ",,,,,,,0,,,, 2022-07-20 00:00:05.249087 PDT,"fenxi","demo",p18782,th-1126980032,"10.19.134.27","32525",2022-07-19 23:31:49 PDT,0,con1827,cmd80,seg-1,,,,,"LOG","00000","context: 1, 104112, 3664, 104112, 0, Timezones ",,,,,,,0,,,, 2022-07-20 00:00:05.249113 PDT,"fenxi","demo",p18782,th-1126980032,"10.19.134.27","32525",2022-07-19 23:31:49 PDT,0,con1827,cmd80,seg-1,,,,,"LOG","00000","context: 1, 8192, 7216, 40960, 32768, MemoryAccountMemoryContext ",,,,,,,0,,,, 2022-07-20 00:00:05.249145 PDT,"fenxi","demo",p18782,th-1126980032,"10.19.134.27","32525",2022-07-19 23:31:49 PDT,0,con1827,cmd80,seg-1,,,,,"LOG","00000","context: 1, 8192, 8016, 59432, 51240, ErrorContext
minirepro file is too large, here is the link
https://download2.moojing.com/tmp/minirepro.data
Hi, with your minirepro, in my local env, ORCA can produce a very good plan:
demo=# explain select * from item2_jd t1 join itemattr2_jd t2 on t1.cat1=t2.cat1 and t1.time=t2.time and t1.item_id=t2.item_id where (t1.cat1='1316' or t1.cat1='737') and t1.time in ('2022-06-01', '2022-05-01', '2022-04-01', '2022-03-01', '2022-02-01', '2022-01-01') order by t1.sales desc limit 10;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..457628.78 rows=4 width=386)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..457628.78 rows=10 width=386)
Merge Key: item2_jd.sales
-> Limit (cost=0.00..457628.77 rows=4 width=386)
-> Sort (cost=0.00..457628.76 rows=6843858 width=386)
Sort Key: item2_jd.sales
-> Hash Join (cost=0.00..117518.94 rows=6843858 width=386)
Hash Cond: ((itemattr2_jd.cat1 = (item2_jd.cat1)::text) AND (itemattr2_jd."time" = item2_jd."time") AND ((itemattr2_jd.item_id)::text = (item2_jd.item_id)::text))
-> Dynamic Seq Scan on itemattr2_jd (dynamic scan id: 2) (cost=0.00..10507.98 rows=48340136 width=56)
Filter: ((("time" = '2022-01-01'::date) OR ("time" = '2022-02-01'::date) OR ("time" = '2022-03-01'::date) OR ("time" = '2022-04-01'::date) OR ("time" = '2022-05-01'::date) OR ("time" = '2022-06-0
1'::date)) AND ((cat1 = '1316'::text) OR (cat1 = '737'::text)))
-> Hash (cost=100.00..100.00 rows=34 width=4)
-> Partition Selector for itemattr2_jd (dynamic scan id: 2) (cost=10.00..100.00 rows=34 width=4)
-> Sequence (cost=0.00..3152.76 rows=3132786 width=330)
-> Partition Selector for item2_jd (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4)
Partitions selected: 2 (out of 2760)
-> Dynamic Seq Scan on item2_jd (dynamic scan id: 1) (cost=0.00..3152.76 rows=3132786 width=330)
Filter: ((((cat1)::text = '1316'::text) OR ((cat1)::text = '737'::text)) AND ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) AND (
("time" = '2022-01-01'::date) OR ("time" = '2022-02-01'::date) OR ("time" = '2022-03-01'::date) OR ("time" = '2022-04-01'::date) OR ("time" = '2022-05-01'::date) OR ("time" = '2022-06-01'::date)))
Optimizer: Pivotal Optimizer (GPORCA)
(18 rows)
From the log you uploaded, it print out MemoryContext Stats, which means there happens OOM in this segment. Since the memorycontext related log is not full, so I cannot say anything on OOM.
A guess why you hit the error message (ORCA fallback), maybe due to ORCA's memory allocation request fails.
Question: if you run the explain in a new session, will it error out?
emo=# explain analyze select * from item2_jd t1 join itemattr2_jd t2 on t1.cat1=t2.cat1 and t1.time=t2.time and t1.item_id=t2.item_id where (t1.cat1='1316' or t1.cat1='737') and t1.time in ('2022-06-01', '2022-05-01', '2022-04-01', '2022-03-01', '2022-02-01', '2022-01-01') order by t1.sales desc limit 10; QUERY PLAN
Limit (cost=2338416275.23..2338416275.45 rows=10 width=398) (actual time=46692.952..46693.295 rows=10 loops=1) -> Gather Motion 144:1 (slice3; segments: 144) (cost=2338416275.23..2338416275.45 rows=10 width=398) (actual time=46692.907..46692.912 rows=10 loops=1) Merge Key: t1.sales -> Limit (cost=2338416275.23..2338416275.25 rows=1 width=398) (actual time=45697.667..45697.672 rows=10 loops=1) -> Sort (cost=2338416275.23..2338416275.30 rows=1 width=398) (actual time=45697.658..45697.658 rows=10 loops=1) Sort Key: t1.sales Sort Method: top-N heapsort Memory: 3824kB -> Hash Join (cost=174490590.10..2338416274.56 rows=1 width=398) (actual time=34669.695..40483.778 rows=663422 loops=1) Hash Cond: (((t1.cat1)::text = t2.cat1) AND (t1."time" = t2."time") AND ((t1.item_id)::text = (t2.item_id)::text)) Extra Text: (seg6) Initial batch 0: (seg6) Wrote 995340K bytes to inner workfile. (seg6) Wrote 10726K bytes to outer workfile. (seg6) Initial batches 1..7: (seg6) Read 865359K bytes from inner workfile: 123623K avg x 7 nonempty batches, 124197K max. (seg6) Wrote 401131K bytes to inner workfile: 57305K avg x 7 overflowing batches, 57576K max. (seg6) Read 5014K bytes from outer workfile: 717K avg x 7 nonempty batches, 755K max. (seg6) Overflow batches 8..15: (seg6) Read 531112K bytes from inner workfile: 66389K avg x 8 nonempty batches, 66777K max. (seg6) Read 5712K bytes from outer workfile: 714K avg x 8 nonempty batches, 743K max. (seg6) Hash chain length 17.9 avg, 217 max, using 723705 of 8388608 buckets. -> Redistribute Motion 144:144 (slice1; segments: 144) (cost=0.00..652799.56 rows=32274 width=339) (actual time=1.137..25.078 rows=32937 loops=1) Hash Key: t1.cat1, t1."time", t1.item_id -> Append (cost=0.00..559853.15 rows=32274 width=339) (actual time=73.701..1060.912 rows=33409 loops=1) -> Seq Scan on item2_jd_1_prt_other_time_2_prt_cat1316 t1 (cost=0.00..227024.18 rows=16104 width=337) (actual time=128.227..138.103 rows=17041 loops=1) Filter: ((((cat1)::text = '1316'::text) OR ((cat1)::text = '737'::text)) AND ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[]))) -> Seq Scan on item2_jd_1_prt_other_time_2_prt_cat737 t1_1 (cost=0.00..224437.62 rows=16121 width=342) (actual time=349.548..358.245 rows=16634 loops=1) Filter: ((((cat1)::text = '1316'::text) OR ((cat1)::text = '737'::text)) AND ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[]))) -> Seq Scan on item2_jd_1_prt_other_time_2_prt_other_cat t1_2 (cost=0.00..108391.34 rows=49 width=329) (never executed) Filter: ((((cat1)::text = '1316'::text) OR ((cat1)::text = '737'::text)) AND ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[]))) -> Hash (cost=137055002.88..137055002.88 rows=11039131 width=59) (actual time=34663.935..34663.935 rows=12938929 loops=1) -> Redistribute Motion 144:144 (slice2; segments: 144) (cost=0.00..137055002.88 rows=12878986 width=59) (actual time=408.181..28160.814 rows=12938929 loops=1) Hash Key: t2.cat1, t2."time", t2.item_id -> Append (cost=0.00..99963526.06 rows=12878986 width=59) (actual time=544.258..19934.171 rows=12963190 loops=1) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat11729 t2 (cost=0.00..5397842.80 rows=690366 width=58) (actual time=273.275..406.581 rows=711429 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat12218 t2_1 (cost=0.00..1016589.12 rows=125140 width=61) (actual time=75.623..115.612 rows=131753 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat12259 t2_2 (cost=0.00..534320.57 rows=64433 width=60) (actual time=46.907..63.841 rows=71481 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat12379 t2_3 (cost=0.00..5513.07 rows=769 width=71) (actual time=91.927..94.746 rows=3247 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat12473 t2_4 (cost=0.00..1036318.32 rows=131821 width=64) (actual time=567.099..605.464 rows=138792 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat1315 t2_5 (cost=0.00..18063635.92 rows=2423733 width=56) (actual time=1121.203..1711.086 rows=2436335 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat1316 t2_6 (cost=0.00..2130487.76 rows=260751 width=63) (actual time=173.793..269.509 rows=274251 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat1318 t2_7 (cost=0.00..7563846.00 rows=937581 width=59) (actual time=1591.971..1919.369 rows=964717 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat1319 t2_8 (cost=0.00..5879914.88 rows=750253 width=58) (actual time=1145.533..1368.125 rows=767602 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat1320 t2_9 (cost=0.00..4790424.72 rows=635785 width=59) (actual time=307.165..955.623 rows=645727 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat13314 t2_10 (cost=0.00..3604881.32 rows=517804 width=68) (actual time=779.756..887.742 rows=541556 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat13765 t2_11 (cost=0.00..708022.81 rows=86313 width=67) (actual time=127.949..159.558 rows=92151 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat13887 t2_12 (cost=0.00..77455.67 rows=8113 width=63) (actual time=79.784..82.015 rows=9362 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat14065 t2_13 (cost=0.00..1004878.68 rows=148961 width=59) (actual time=58.451..103.764 rows=156068 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat15083 t2_14 (cost=0.00..1.86 rows=1 width=62) (actual time=21.569..21.593 rows=8 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat15248 t2_15 (cost=0.00..2018184.64 rows=249803 width=63) (actual time=176.250..241.628 rows=259749 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat15901 t2_16 (cost=0.00..1327416.68 rows=175333 width=61) (actual time=94.899..125.426 rows=183338 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat15980 t2_17 (cost=0.00..2103.55 rows=334 width=66) (actual time=5.533..5.764 rows=594 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat1620 t2_18 (cost=0.00..3082954.44 rows=392047 width=60) (actual time=239.929..374.022 rows=403841 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat1672 t2_19 (cost=0.00..531218.66 rows=43596 width=61) (actual time=98.866..112.943 rows=46820 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat16750 t2_20 (cost=0.00..1666562.30 rows=218864 width=62) (actual time=93.805..159.552 rows=229642 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat1713 t2_21 (cost=0.00..2545617.98 rows=481048 width=49) (actual time=327.519..411.012 rows=493994 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat17329 t2_22 (cost=0.00..1807891.22 rows=186623 width=59) (actual time=634.578..684.877 rows=193204 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat4053 t2_23 (cost=0.00..108984.64 rows=8989 width=50) (actual time=34.048..36.790 rows=10419 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat4938 t2_24 (cost=0.00..90394.48 rows=12439 width=61) (actual time=26.500..29.168 rows=14038 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat5025 t2_25 (cost=0.00..898649.84 rows=108367 width=59) (actual time=310.805..355.407 rows=115420 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat6144 t2_26 (cost=0.00..1311288.60 rows=155094 width=61) (actual time=128.958..172.389 rows=162612 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat6196 t2_27 (cost=0.00..3044295.80 rows=386323 width=59) (actual time=1419.487..1505.160 rows=396354 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat6233 t2_28 (cost=0.00..1623083.10 rows=207774 width=62) (actual time=775.834..818.703 rows=217128 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat652 t2_29 (cost=0.00..1288253.50 rows=161830 width=58) (actual time=585.007..624.571 rows=168945 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat670 t2_30 (cost=0.00..5680981.44 rows=724291 width=56) (actual time=1280.917..1491.449 rows=741375 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat6728 t2_31 (cost=0.00..3463186.16 rows=395339 width=61) (actual time=199.051..321.725 rows=411493 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat6994 t2_32 (cost=0.00..1026284.98 rows=124908 width=59) (actual time=630.222..679.511 rows=135908 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat737 t2_33 (cost=0.00..3132972.40 rows=387480 width=60) (actual time=222.377..328.347 rows=401519 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat9192 t2_34 (cost=0.00..2133760.30 rows=284377 width=61) (actual time=895.546..971.455 rows=302997 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat9259 t2_35 (cost=0.00..6.81 rows=1 width=52) (never executed) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat9847 t2_36 (cost=0.00..2734891.80 rows=292872 width=61) (actual time=244.795..321.577 rows=303216 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat9855 t2_37 (cost=0.00..4869953.56 rows=562910 width=59) (actual time=282.729..386.717 rows=587712 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat9987 t2_38 (cost=0.00..2859865.12 rows=416211 width=59) (actual time=852.566..924.957 rows=430657 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat18528 t2_39 (cost=0.00..751.24 rows=90 width=63) (actual time=2.030..2.120 rows=329 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat18054 t2_40 (cost=0.00..126.77 rows=4 width=63) (actual time=1.790..1.826 rows=70 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat21288 t2_41 (cost=0.00..183.12 rows=1 width=58) (never executed) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat18017 t2_42 (cost=0.00..50.12 rows=7 width=58) (actual time=1.851..1.884 rows=129 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat18008 t2_43 (cost=0.00..26.73 rows=6 width=58) (actual time=3.914..3.967 rows=138 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat13678 t2_44 (cost=0.00..27378.16 rows=3529 width=66) (actual time=302.751..303.919 rows=4238 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_other_cat t2_45 (cost=0.00..872074.44 rows=116695 width=60) (actual time=54.847..83.421 rows=125932 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) Planning time: 565.106 ms (slice0) Executor memory: 49527K bytes. (slice1) Executor memory: 6367K bytes avg x 144 workers, 6367K bytes max (seg0). (slice2) Executor memory: 33751K bytes avg x 144 workers, 33759K bytes max (seg125).
- (slice3) Executor memory: 218373K bytes avg x 144 workers, 218403K bytes max (seg3). Work_mem: 130773K bytes max, 1213086K bytes wanted. Memory used: 262144kB Memory wanted: 2431870kB Optimizer: Postgres query optimizer Execution time: 46929.537 ms (134 rows)
emo=# explain analyze select * from item2_jd t1 join itemattr2_jd t2 on t1.cat1=t2.cat1 and t1.time=t2.time and t1.item_id=t2.item_id where (t1.cat1='1316' or t1.cat1='737') and t1.time in ('2022-06-01', '2022-05-01', '2022-04-01', '2022-03-01', '2022-02-01', '2022-01-01') order by t1.sales desc limit 10; QUERY PLAN
Limit (cost=2338416275.23..2338416275.45 rows=10 width=398) (actual time=46692.952..46693.295 rows=10 loops=1) -> Gather Motion 144:1 (slice3; segments: 144) (cost=2338416275.23..2338416275.45 rows=10 width=398) (actual time=46692.907..46692.912 rows=10 loops=1) Merge Key: t1.sales -> Limit (cost=2338416275.23..2338416275.25 rows=1 width=398) (actual time=45697.667..45697.672 rows=10 loops=1) -> Sort (cost=2338416275.23..2338416275.30 rows=1 width=398) (actual time=45697.658..45697.658 rows=10 loops=1) Sort Key: t1.sales Sort Method: top-N heapsort Memory: 3824kB -> Hash Join (cost=174490590.10..2338416274.56 rows=1 width=398) (actual time=34669.695..40483.778 rows=663422 loops=1) Hash Cond: (((t1.cat1)::text = t2.cat1) AND (t1."time" = t2."time") AND ((t1.item_id)::text = (t2.item_id)::text)) Extra Text: (seg6) Initial batch 0: (seg6) Wrote 995340K bytes to inner workfile. (seg6) Wrote 10726K bytes to outer workfile. (seg6) Initial batches 1..7: (seg6) Read 865359K bytes from inner workfile: 123623K avg x 7 nonempty batches, 124197K max. (seg6) Wrote 401131K bytes to inner workfile: 57305K avg x 7 overflowing batches, 57576K max. (seg6) Read 5014K bytes from outer workfile: 717K avg x 7 nonempty batches, 755K max. (seg6) Overflow batches 8..15: (seg6) Read 531112K bytes from inner workfile: 66389K avg x 8 nonempty batches, 66777K max. (seg6) Read 5712K bytes from outer workfile: 714K avg x 8 nonempty batches, 743K max. (seg6) Hash chain length 17.9 avg, 217 max, using 723705 of 8388608 buckets. -> Redistribute Motion 144:144 (slice1; segments: 144) (cost=0.00..652799.56 rows=32274 width=339) (actual time=1.137..25.078 rows=32937 loops=1) Hash Key: t1.cat1, t1."time", t1.item_id -> Append (cost=0.00..559853.15 rows=32274 width=339) (actual time=73.701..1060.912 rows=33409 loops=1) -> Seq Scan on item2_jd_1_prt_other_time_2_prt_cat1316 t1 (cost=0.00..227024.18 rows=16104 width=337) (actual time=128.227..138.103 rows=17041 loops=1) Filter: ((((cat1)::text = '1316'::text) OR ((cat1)::text = '737'::text)) AND ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[]))) -> Seq Scan on item2_jd_1_prt_other_time_2_prt_cat737 t1_1 (cost=0.00..224437.62 rows=16121 width=342) (actual time=349.548..358.245 rows=16634 loops=1) Filter: ((((cat1)::text = '1316'::text) OR ((cat1)::text = '737'::text)) AND ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[]))) -> Seq Scan on item2_jd_1_prt_other_time_2_prt_other_cat t1_2 (cost=0.00..108391.34 rows=49 width=329) (never executed) Filter: ((((cat1)::text = '1316'::text) OR ((cat1)::text = '737'::text)) AND ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[]))) -> Hash (cost=137055002.88..137055002.88 rows=11039131 width=59) (actual time=34663.935..34663.935 rows=12938929 loops=1) -> Redistribute Motion 144:144 (slice2; segments: 144) (cost=0.00..137055002.88 rows=12878986 width=59) (actual time=408.181..28160.814 rows=12938929 loops=1) Hash Key: t2.cat1, t2."time", t2.item_id -> Append (cost=0.00..99963526.06 rows=12878986 width=59) (actual time=544.258..19934.171 rows=12963190 loops=1) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat11729 t2 (cost=0.00..5397842.80 rows=690366 width=58) (actual time=273.275..406.581 rows=711429 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat12218 t2_1 (cost=0.00..1016589.12 rows=125140 width=61) (actual time=75.623..115.612 rows=131753 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat12259 t2_2 (cost=0.00..534320.57 rows=64433 width=60) (actual time=46.907..63.841 rows=71481 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat12379 t2_3 (cost=0.00..5513.07 rows=769 width=71) (actual time=91.927..94.746 rows=3247 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat12473 t2_4 (cost=0.00..1036318.32 rows=131821 width=64) (actual time=567.099..605.464 rows=138792 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat1315 t2_5 (cost=0.00..18063635.92 rows=2423733 width=56) (actual time=1121.203..1711.086 rows=2436335 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat1316 t2_6 (cost=0.00..2130487.76 rows=260751 width=63) (actual time=173.793..269.509 rows=274251 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat1318 t2_7 (cost=0.00..7563846.00 rows=937581 width=59) (actual time=1591.971..1919.369 rows=964717 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat1319 t2_8 (cost=0.00..5879914.88 rows=750253 width=58) (actual time=1145.533..1368.125 rows=767602 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat1320 t2_9 (cost=0.00..4790424.72 rows=635785 width=59) (actual time=307.165..955.623 rows=645727 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat13314 t2_10 (cost=0.00..3604881.32 rows=517804 width=68) (actual time=779.756..887.742 rows=541556 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat13765 t2_11 (cost=0.00..708022.81 rows=86313 width=67) (actual time=127.949..159.558 rows=92151 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat13887 t2_12 (cost=0.00..77455.67 rows=8113 width=63) (actual time=79.784..82.015 rows=9362 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat14065 t2_13 (cost=0.00..1004878.68 rows=148961 width=59) (actual time=58.451..103.764 rows=156068 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat15083 t2_14 (cost=0.00..1.86 rows=1 width=62) (actual time=21.569..21.593 rows=8 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat15248 t2_15 (cost=0.00..2018184.64 rows=249803 width=63) (actual time=176.250..241.628 rows=259749 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat15901 t2_16 (cost=0.00..1327416.68 rows=175333 width=61) (actual time=94.899..125.426 rows=183338 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat15980 t2_17 (cost=0.00..2103.55 rows=334 width=66) (actual time=5.533..5.764 rows=594 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat1620 t2_18 (cost=0.00..3082954.44 rows=392047 width=60) (actual time=239.929..374.022 rows=403841 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat1672 t2_19 (cost=0.00..531218.66 rows=43596 width=61) (actual time=98.866..112.943 rows=46820 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat16750 t2_20 (cost=0.00..1666562.30 rows=218864 width=62) (actual time=93.805..159.552 rows=229642 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat1713 t2_21 (cost=0.00..2545617.98 rows=481048 width=49) (actual time=327.519..411.012 rows=493994 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat17329 t2_22 (cost=0.00..1807891.22 rows=186623 width=59) (actual time=634.578..684.877 rows=193204 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat4053 t2_23 (cost=0.00..108984.64 rows=8989 width=50) (actual time=34.048..36.790 rows=10419 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat4938 t2_24 (cost=0.00..90394.48 rows=12439 width=61) (actual time=26.500..29.168 rows=14038 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat5025 t2_25 (cost=0.00..898649.84 rows=108367 width=59) (actual time=310.805..355.407 rows=115420 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat6144 t2_26 (cost=0.00..1311288.60 rows=155094 width=61) (actual time=128.958..172.389 rows=162612 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat6196 t2_27 (cost=0.00..3044295.80 rows=386323 width=59) (actual time=1419.487..1505.160 rows=396354 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat6233 t2_28 (cost=0.00..1623083.10 rows=207774 width=62) (actual time=775.834..818.703 rows=217128 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat652 t2_29 (cost=0.00..1288253.50 rows=161830 width=58) (actual time=585.007..624.571 rows=168945 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat670 t2_30 (cost=0.00..5680981.44 rows=724291 width=56) (actual time=1280.917..1491.449 rows=741375 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat6728 t2_31 (cost=0.00..3463186.16 rows=395339 width=61) (actual time=199.051..321.725 rows=411493 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat6994 t2_32 (cost=0.00..1026284.98 rows=124908 width=59) (actual time=630.222..679.511 rows=135908 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat737 t2_33 (cost=0.00..3132972.40 rows=387480 width=60) (actual time=222.377..328.347 rows=401519 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat9192 t2_34 (cost=0.00..2133760.30 rows=284377 width=61) (actual time=895.546..971.455 rows=302997 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat9259 t2_35 (cost=0.00..6.81 rows=1 width=52) (never executed) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat9847 t2_36 (cost=0.00..2734891.80 rows=292872 width=61) (actual time=244.795..321.577 rows=303216 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat9855 t2_37 (cost=0.00..4869953.56 rows=562910 width=59) (actual time=282.729..386.717 rows=587712 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat9987 t2_38 (cost=0.00..2859865.12 rows=416211 width=59) (actual time=852.566..924.957 rows=430657 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat18528 t2_39 (cost=0.00..751.24 rows=90 width=63) (actual time=2.030..2.120 rows=329 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat18054 t2_40 (cost=0.00..126.77 rows=4 width=63) (actual time=1.790..1.826 rows=70 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat21288 t2_41 (cost=0.00..183.12 rows=1 width=58) (never executed) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat18017 t2_42 (cost=0.00..50.12 rows=7 width=58) (actual time=1.851..1.884 rows=129 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat18008 t2_43 (cost=0.00..26.73 rows=6 width=58) (actual time=3.914..3.967 rows=138 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat13678 t2_44 (cost=0.00..27378.16 rows=3529 width=66) (actual time=302.751..303.919 rows=4238 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_other_cat t2_45 (cost=0.00..872074.44 rows=116695 width=60) (actual time=54.847..83.421 rows=125932 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) Planning time: 565.106 ms (slice0) Executor memory: 49527K bytes. (slice1) Executor memory: 6367K bytes avg x 144 workers, 6367K bytes max (seg0). (slice2) Executor memory: 33751K bytes avg x 144 workers, 33759K bytes max (seg125).
- (slice3) Executor memory: 218373K bytes avg x 144 workers, 218403K bytes max (seg3). Work_mem: 130773K bytes max, 1213086K bytes wanted. Memory used: 262144kB Memory wanted: 2431870kB Optimizer: Postgres query optimizer Execution time: 46929.537 ms (134 rows)
demo=# explain analyze select * from item2_jd t1 join itemattr2_jd t2 on t1.cat1=t2.cat1 and t1.time=t2.time and t1.item_id=t2.item_id where (t1.cat1='1316' or t1.cat1='737') and t1.time in ('2022-06-01', '2022-05-01', '2022-04-01', '2022-03-01', '2022-02-01', '2022-01-01') order by t1.sales desc limit 10; QUERY PLAN
Limit (cost=2338416275.23..2338416275.45 rows=10 width=398) (actual time=46692.952..46693.295 rows=10 loops=1) -> Gather Motion 144:1 (slice3; segments: 144) (cost=2338416275.23..2338416275.45 rows=10 width=398) (actual time=46692.907..46692.912 rows=10 loops=1) Merge Key: t1.sales -> Limit (cost=2338416275.23..2338416275.25 rows=1 width=398) (actual time=45697.667..45697.672 rows=10 loops=1) -> Sort (cost=2338416275.23..2338416275.30 rows=1 width=398) (actual time=45697.658..45697.658 rows=10 loops=1) Sort Key: t1.sales Sort Method: top-N heapsort Memory: 3824kB -> Hash Join (cost=174490590.10..2338416274.56 rows=1 width=398) (actual time=34669.695..40483.778 rows=663422 loops=1) Hash Cond: (((t1.cat1)::text = t2.cat1) AND (t1."time" = t2."time") AND ((t1.item_id)::text = (t2.item_id)::text)) Extra Text: (seg6) Initial batch 0: (seg6) Wrote 995340K bytes to inner workfile. (seg6) Wrote 10726K bytes to outer workfile. (seg6) Initial batches 1..7: (seg6) Read 865359K bytes from inner workfile: 123623K avg x 7 nonempty batches, 124197K max. (seg6) Wrote 401131K bytes to inner workfile: 57305K avg x 7 overflowing batches, 57576K max. (seg6) Read 5014K bytes from outer workfile: 717K avg x 7 nonempty batches, 755K max. (seg6) Overflow batches 8..15: (seg6) Read 531112K bytes from inner workfile: 66389K avg x 8 nonempty batches, 66777K max. (seg6) Read 5712K bytes from outer workfile: 714K avg x 8 nonempty batches, 743K max. (seg6) Hash chain length 17.9 avg, 217 max, using 723705 of 8388608 buckets. -> Redistribute Motion 144:144 (slice1; segments: 144) (cost=0.00..652799.56 rows=32274 width=339) (actual time=1.137..25.078 rows=32937 loops=1) Hash Key: t1.cat1, t1."time", t1.item_id -> Append (cost=0.00..559853.15 rows=32274 width=339) (actual time=73.701..1060.912 rows=33409 loops=1) -> Seq Scan on item2_jd_1_prt_other_time_2_prt_cat1316 t1 (cost=0.00..227024.18 rows=16104 width=337) (actual time=128.227..138.103 rows=17041 loops=1) Filter: ((((cat1)::text = '1316'::text) OR ((cat1)::text = '737'::text)) AND ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[]))) -> Seq Scan on item2_jd_1_prt_other_time_2_prt_cat737 t1_1 (cost=0.00..224437.62 rows=16121 width=342) (actual time=349.548..358.245 rows=16634 loops=1) Filter: ((((cat1)::text = '1316'::text) OR ((cat1)::text = '737'::text)) AND ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[]))) -> Seq Scan on item2_jd_1_prt_other_time_2_prt_other_cat t1_2 (cost=0.00..108391.34 rows=49 width=329) (never executed) Filter: ((((cat1)::text = '1316'::text) OR ((cat1)::text = '737'::text)) AND ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[]))) -> Hash (cost=137055002.88..137055002.88 rows=11039131 width=59) (actual time=34663.935..34663.935 rows=12938929 loops=1) -> Redistribute Motion 144:144 (slice2; segments: 144) (cost=0.00..137055002.88 rows=12878986 width=59) (actual time=408.181..28160.814 rows=12938929 loops=1) Hash Key: t2.cat1, t2."time", t2.item_id -> Append (cost=0.00..99963526.06 rows=12878986 width=59) (actual time=544.258..19934.171 rows=12963190 loops=1) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat11729 t2 (cost=0.00..5397842.80 rows=690366 width=58) (actual time=273.275..406.581 rows=711429 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat12218 t2_1 (cost=0.00..1016589.12 rows=125140 width=61) (actual time=75.623..115.612 rows=131753 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat12259 t2_2 (cost=0.00..534320.57 rows=64433 width=60) (actual time=46.907..63.841 rows=71481 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat12379 t2_3 (cost=0.00..5513.07 rows=769 width=71) (actual time=91.927..94.746 rows=3247 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat12473 t2_4 (cost=0.00..1036318.32 rows=131821 width=64) (actual time=567.099..605.464 rows=138792 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat1315 t2_5 (cost=0.00..18063635.92 rows=2423733 width=56) (actual time=1121.203..1711.086 rows=2436335 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat1316 t2_6 (cost=0.00..2130487.76 rows=260751 width=63) (actual time=173.793..269.509 rows=274251 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat1318 t2_7 (cost=0.00..7563846.00 rows=937581 width=59) (actual time=1591.971..1919.369 rows=964717 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat1319 t2_8 (cost=0.00..5879914.88 rows=750253 width=58) (actual time=1145.533..1368.125 rows=767602 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat1320 t2_9 (cost=0.00..4790424.72 rows=635785 width=59) (actual time=307.165..955.623 rows=645727 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat13314 t2_10 (cost=0.00..3604881.32 rows=517804 width=68) (actual time=779.756..887.742 rows=541556 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat13765 t2_11 (cost=0.00..708022.81 rows=86313 width=67) (actual time=127.949..159.558 rows=92151 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat13887 t2_12 (cost=0.00..77455.67 rows=8113 width=63) (actual time=79.784..82.015 rows=9362 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat14065 t2_13 (cost=0.00..1004878.68 rows=148961 width=59) (actual time=58.451..103.764 rows=156068 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat15083 t2_14 (cost=0.00..1.86 rows=1 width=62) (actual time=21.569..21.593 rows=8 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat15248 t2_15 (cost=0.00..2018184.64 rows=249803 width=63) (actual time=176.250..241.628 rows=259749 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat15901 t2_16 (cost=0.00..1327416.68 rows=175333 width=61) (actual time=94.899..125.426 rows=183338 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat15980 t2_17 (cost=0.00..2103.55 rows=334 width=66) (actual time=5.533..5.764 rows=594 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat1620 t2_18 (cost=0.00..3082954.44 rows=392047 width=60) (actual time=239.929..374.022 rows=403841 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat1672 t2_19 (cost=0.00..531218.66 rows=43596 width=61) (actual time=98.866..112.943 rows=46820 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat16750 t2_20 (cost=0.00..1666562.30 rows=218864 width=62) (actual time=93.805..159.552 rows=229642 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat1713 t2_21 (cost=0.00..2545617.98 rows=481048 width=49) (actual time=327.519..411.012 rows=493994 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat17329 t2_22 (cost=0.00..1807891.22 rows=186623 width=59) (actual time=634.578..684.877 rows=193204 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat4053 t2_23 (cost=0.00..108984.64 rows=8989 width=50) (actual time=34.048..36.790 rows=10419 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat4938 t2_24 (cost=0.00..90394.48 rows=12439 width=61) (actual time=26.500..29.168 rows=14038 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat5025 t2_25 (cost=0.00..898649.84 rows=108367 width=59) (actual time=310.805..355.407 rows=115420 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat6144 t2_26 (cost=0.00..1311288.60 rows=155094 width=61) (actual time=128.958..172.389 rows=162612 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat6196 t2_27 (cost=0.00..3044295.80 rows=386323 width=59) (actual time=1419.487..1505.160 rows=396354 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat6233 t2_28 (cost=0.00..1623083.10 rows=207774 width=62) (actual time=775.834..818.703 rows=217128 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat652 t2_29 (cost=0.00..1288253.50 rows=161830 width=58) (actual time=585.007..624.571 rows=168945 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat670 t2_30 (cost=0.00..5680981.44 rows=724291 width=56) (actual time=1280.917..1491.449 rows=741375 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat6728 t2_31 (cost=0.00..3463186.16 rows=395339 width=61) (actual time=199.051..321.725 rows=411493 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat6994 t2_32 (cost=0.00..1026284.98 rows=124908 width=59) (actual time=630.222..679.511 rows=135908 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat737 t2_33 (cost=0.00..3132972.40 rows=387480 width=60) (actual time=222.377..328.347 rows=401519 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat9192 t2_34 (cost=0.00..2133760.30 rows=284377 width=61) (actual time=895.546..971.455 rows=302997 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat9259 t2_35 (cost=0.00..6.81 rows=1 width=52) (never executed) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat9847 t2_36 (cost=0.00..2734891.80 rows=292872 width=61) (actual time=244.795..321.577 rows=303216 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat9855 t2_37 (cost=0.00..4869953.56 rows=562910 width=59) (actual time=282.729..386.717 rows=587712 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat9987 t2_38 (cost=0.00..2859865.12 rows=416211 width=59) (actual time=852.566..924.957 rows=430657 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat18528 t2_39 (cost=0.00..751.24 rows=90 width=63) (actual time=2.030..2.120 rows=329 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat18054 t2_40 (cost=0.00..126.77 rows=4 width=63) (actual time=1.790..1.826 rows=70 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat21288 t2_41 (cost=0.00..183.12 rows=1 width=58) (never executed) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat18017 t2_42 (cost=0.00..50.12 rows=7 width=58) (actual time=1.851..1.884 rows=129 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat18008 t2_43 (cost=0.00..26.73 rows=6 width=58) (actual time=3.914..3.967 rows=138 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_cat13678 t2_44 (cost=0.00..27378.16 rows=3529 width=66) (actual time=302.751..303.919 rows=4238 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) -> Seq Scan on itemattr2_jd_1_prt_other_time_2_prt_other_cat t2_45 (cost=0.00..872074.44 rows=116695 width=60) (actual time=54.847..83.421 rows=125932 loops=1) Filter: ("time" = ANY ('{2022-06-01,2022-05-01,2022-04-01,2022-03-01,2022-02-01,2022-01-01}'::date[])) Planning time: 565.106 ms (slice0) Executor memory: 49527K bytes. (slice1) Executor memory: 6367K bytes avg x 144 workers, 6367K bytes max (seg0). (slice2) Executor memory: 33751K bytes avg x 144 workers, 33759K bytes max (seg125).
- (slice3) Executor memory: 218373K bytes avg x 144 workers, 218403K bytes max (seg3). Work_mem: 130773K bytes max, 1213086K bytes wanted. Memory used: 262144kB Memory wanted: 2431870kB Optimizer: Postgres query optimizer Execution time: 46929.537 ms (134 rows)
Our gp config :
work_mem=3398667 statement_mem=256MB max_statement_mem=4000MB gp_vmem_protect_limit=8192 optimizer=on
System config: vm.overcommit_memory = 2 vm.overcommit_ratio = 95 # See Segment Host Memory
Memory info: master 1 192GB memory hosts 7 192GB memory segments 24/per host no mirror
ORCA can produce a very good plan for this sql under last week.
When we found a bad file in segment 1, and truncate this seg; the issue happen.
For the bad file log here:
demo=# analyze itemattr2; ERROR: header checksum does not match, expected 0x00001FF5 and found 0x069AD64A (seg1 10.19.183.130:6001 pid=15037) DETAIL: Append-Only storage header kind 0 unknown CONTEXT: Scan of Append-Only Column-Oriented relation 'itemattr2_1_prt_other_time_2_prt_cat50011699', column #7 'sold'. Append-Only segment file 'base/16384/9980218.769', block header offset in file = 3145728, bufferCount 97
Is there anything changed about your desk? Since checksum mismatching only could happen at desk damage.
Our gpdb auto enter recovery mode, when we are doing gpexpand and Redistribute tables.
Now we found the problem, the gpexpand don't finish yet, some tables always has less segments(144). But gpdb has 168 segs.
We test this sql under new table, it's work fine.
Thank u~