Andrei,
Yes, from explain output, since optimizer already get the merge_append
cost but not take account into total cost, that make a big difference. I
shared table DDLs and explain analyze,buffers output , I think the data
maybe generated by other way to reproduce this issue. sorry for not sharing
the commercial production data here.
Andrei Lepikhov <[email protected]> 於 2025年4月2日週三 下午7:03寫道：
> On 4/2/25 12:18, James Pang wrote:
> > Hi,
> > Postgresq v14.8, we found optimizer doest not take "merge append"
> > cost into sql plan total cost and then make a bad sql plan. attached
> > please find details.
> I suppose there is a different type of issue.
> MegeJoin sometimes doesn't need to scan the whole inner or outer side
> (see the MergeScanSelCache structure and how it is used in the cost
> estimation routine).
>
> So, the cost can be less because the optimizer predicted that only a
> small part of the Append will be scanned and used some sort of
> interpolation between startup cost and total cost.
>
> But to be sure, could you send the results of EXPLAIN ANALYZE VERBOSE?
> If you also send the data to reproduce the case, we may find the source
> of the problem more precisely.
>
> --
> regards, Andrei Lepikhov
>
\d+ tablea
Partitioned table "mct.tablea"
Column | Type | Collation | Nullable |
Default | Storage | Compression | Stats target | Description
-------------------+-----------------------------+-----------+----------+-----------+----------+-------------+--------------+-------------
mmm_id | bigint | | not null |
| plain | | |
clu_id | bigint | | |
| plain | | |
dddd_id | bigint | | |
| plain | | |
zzz_id | bigint | | |
| plain | | |
ssr_id | bigint | | |
| plain | | |
ssr_type | bigint | | |
| plain | | |
mmm_stat | integer | | | |
plain | | |
time | timestamp without time zone | | not null |
| plain | | |
protocol | integer | | |
| plain | | |
uuun | character varying(20) | | |
| extended | | |
lastmodifiedtime | timestamp without time zone | | |
sysdate() | plain | | |
mmm_sss_id | bigint | | | |
plain | | |
remote_addr | character varying(20) | | |
| extended | | |
Partition key: RANGE ("time")
Indexes:
"tablea_pkey" PRIMARY KEY, btree (mmm_id, "time")
"tablea_idx1" btree (zzz_id, mmm_sss_id, mmm_stat)
"tablea_idx2" btree (mmm_sss_id)
Publications:
"xxxx_24503"
Triggers:
xxxxx AFTER DELETE ON tablea FOR EACH ROW WHEN (USER !~~ 'SPLEX%'::text)
EXECUTE FUNCTION trigger_fct_xxxxxx()
tr_tablea_update BEFORE UPDATE ON tablea FOR EACH ROW WHEN (USER !~~
'SPLEX%'::text) EXECUTE FUNCTION trigger_fct_tr_xxxxx
tus_update()
Partitions: tablea_p1970 FOR VALUES FROM ('1970-01-01 00:00:00') TO
('2023-09-07 00:00:00'),
tablea_p20250319 FOR VALUES FROM ('2025-03-19 00:00:00') TO
('2025-03-20 00:00:00'),
tablea_p20250320 FOR VALUES FROM ('2025-03-20 00:00:00') TO
('2025-03-21 00:00:00'),
tablea_p20250321 FOR VALUES FROM ('2025-03-21 00:00:00') TO
('2025-03-22 00:00:00'),
tablea_p20250322 FOR VALUES FROM ('2025-03-22 00:00:00') TO
('2025-03-23 00:00:00'),
tablea_p20250323 FOR VALUES FROM ('2025-03-23 00:00:00') TO
('2025-03-24 00:00:00'),
tablea_p20250324 FOR VALUES FROM ('2025-03-24 00:00:00') TO
('2025-03-25 00:00:00'),
tablea_p20250325 FOR VALUES FROM ('2025-03-25 00:00:00') TO
('2025-03-26 00:00:00'),
tablea_p20250326 FOR VALUES FROM ('2025-03-26 00:00:00') TO
('2025-03-27 00:00:00'),
tablea_p20250327 FOR VALUES FROM ('2025-03-27 00:00:00') TO
('2025-03-28 00:00:00'),
tablea_p20250328 FOR VALUES FROM ('2025-03-28 00:00:00') TO
('2025-03-29 00:00:00'),
tablea_p20250329 FOR VALUES FROM ('2025-03-29 00:00:00') TO
('2025-03-30 00:00:00'),
tablea_p20250330 FOR VALUES FROM ('2025-03-30 00:00:00') TO
('2025-03-31 00:00:00'),
tablea_p20250331 FOR VALUES FROM ('2025-03-31 00:00:00') TO
('2025-04-01 00:00:00'),
tablea_p20250401 FOR VALUES FROM ('2025-04-01 00:00:00') TO
('2025-04-02 00:00:00'),
tablea_p20250402 FOR VALUES FROM ('2025-04-02 00:00:00') TO
('2025-04-03 00:00:00'),
tablea_p20250403 FOR VALUES FROM ('2025-04-03 00:00:00') TO
('2025-04-04 00:00:00'),
tablea_p20250404 FOR VALUES FROM ('2025-04-04 00:00:00') TO
('2025-04-05 00:00:00'),
tablea_p20250405 FOR VALUES FROM ('2025-04-05 00:00:00') TO
('2025-04-06 00:00:00'),
tablea_p20250406 FOR VALUES FROM ('2025-04-06 00:00:00') TO
('2025-04-07 00:00:00'),
tablea_p20250407 FOR VALUES FROM ('2025-04-07 00:00:00') TO
('2025-04-08 00:00:00'),
tablea_p20250408 FOR VALUES FROM ('2025-04-08 00:00:00') TO
('2025-04-09 00:00:00'),
tablea_p20250409 FOR VALUES FROM ('2025-04-09 00:00:00') TO
('2025-04-10 00:00:00'),
tablea_p20250410 FOR VALUES FROM ('2025-04-10 00:00:00') TO
('2025-04-11 00:00:00'),
tablea_p20250411 FOR VALUES FROM ('2025-04-11 00:00:00') TO
('2025-04-12 00:00:00'),
tablea_p20250412 FOR VALUES FROM ('2025-04-12 00:00:00') TO
('2025-04-13 00:00:00'),
tablea_p20250413 FOR VALUES FROM ('2025-04-13 00:00:00') TO
('2025-04-14 00:00:00'),
tablea_p20250414 FOR VALUES FROM ('2025-04-14 00:00:00') TO
('2025-04-15 00:00:00'),
tablea_p20250415 FOR VALUES FROM ('2025-04-15 00:00:00') TO
('2025-04-16 00:00:00'),
tablea_p20250416 FOR VALUES FROM ('2025-04-16 00:00:00') TO
('2025-04-17 00:00:00')
\d+ tableb
Table "tableb"
Column | Type | Collation | Nullable |
Default | Storage | Compression | Stats target | Descr
iption
------------------+-----------------------------+-----------+----------+-------------------------+----------+-------------+--------------+-------------------
---------------------
sss_id | bigint | | not null |
| plain | | |
zzz_id | bigint | | not null |
| plain | | |
sss_ip | character varying(80) | | not null |
| extended | | |
sss_type | bigint | | not null |
| plain | | |
sss_hostname | character varying(80) | | |
| extended | | |
sss_field1 | character varying(256) | | |
| extended | | |
sss_field2 | character varying(256) | | |
| extended | | |
server_field3 | bigint | | | 0
| plain | | |
lastmodifiedtime | timestamp without time zone | | |
sysdate() | plain | | |
sss_field4 | character varying(512) | | |
| extended | | |
sss_field5 | character varying(512) | | |
| extended | | |
sss_field6 | bigint | | | 0
| plain | | |
key_sss | integer | | | 1
| plain | | |
ssstype | character varying(32) | | |
| extended | | |
keyword | character varying(256) | | |
| extended | | |
is_from_ab | smallint | | | 0
| plain | | |
rrr_id | bigint | | | 0
| plain | | |
instance_type | bigint | | | 0
| plain | | |
cc_id | character varying(255) | | |
| extended | | |
pool_sys_id | character varying(50) | | |
''::character varying | extended | | |
memo | character varying(255) | | |
'{}'::character varying | extended | | |
vvv | smallint | | | 0
| plain | | |
real_ip | character varying(100) | | |
''::character varying | extended | | |
vvv_name | character varying(100) | | |
| extended | | |
pool_id | bigint | | |
| plain | | |
ttt_info | character varying(100) | | |
| extended | | |
label | character varying | | |
'{}'::character varying | extended | | |
exp_mmm | integer | | | 0
| plain | | |
Indexes:
"tableb_pkey" PRIMARY KEY, btree (sss_id)
"idx_serverip" btree (sss_ip)
"idxvserver" btree (zzz_id, sss_type)
Foreign-key constraints:
"tableb_zzz_fk" FOREIGN KEY (zzz_id) REFERENCES tablez(zzzz_id)
Referenced by:
TABLE "xxxx_setting" CONSTRAINT "xxxxxx_fk" FOREIGN KEY (sss_id) REFERENCES
tableb(sss_id)
TABLE "yyyyy" CONSTRAINT "yyyyyy_fk" FOREIGN KEY (ssss_id) REFERENCES
tableb(sss_id)
Publications:
"pggmct_24503"
Triggers:
xxxx_update BEFORE UPDATE ON xxxxx FOR EACH ROW WHEN (USER !~~
'SPLEX%'::text) EXECUTE FUNCTION trigger_fct_tr_xxxx()
Replica Identity: FULL
Access method: heap
\d+ tablec
Partitioned table
"mct.tablec"
Column | Type | Collation | Nullable |
Default | Storage | Compression | Stats
target | Description
------------------+-----------------------------+-----------+----------+-----------+----------+-------------+-------
-------+-------------
ccc_id | bigint | | not null |
| plain | |
|
sss_id | bigint | | |
| plain | |
|
cccments | character varying(1024) | | not null |
| extended | |
|
tablec_time | timestamp without time zone | | not null |
sysdate() | plain | |
|
tablec_field1 | integer | | not null | 0
| plain | |
|
lastmodifiedtime | timestamp without time zone | | |
sysdate() | plain | |
|
Partition key: RANGE (tablec_time)
Indexes:
"tablec_pkey" PRIMARY KEY, btree (comment_id, tablec_time)
"sss_id_idx" btree (sss_id)
Triggers:
tr_tablec_update BEFORE UPDATE ON tablec FOR EACH ROW WHEN (USER !~~
'SPLEX%'::text) EXECUTE FUNCTION trigge
r_fct_tr_tablec_update()
Partitions: tablec_p1970 FOR VALUES FROM ('1970-01-01 00:00:00') TO
('2023-09-07 00:00:00'),
tablec_p20250319 FOR VALUES FROM ('2025-03-19 00:00:00') TO
('2025-03-20 00:00:00'),
tablec_p20250320 FOR VALUES FROM ('2025-03-20 00:00:00') TO
('2025-03-21 00:00:00'),
tablec_p20250321 FOR VALUES FROM ('2025-03-21 00:00:00') TO
('2025-03-22 00:00:00'),
tablec_p20250322 FOR VALUES FROM ('2025-03-22 00:00:00') TO
('2025-03-23 00:00:00'),
tablec_p20250323 FOR VALUES FROM ('2025-03-23 00:00:00') TO
('2025-03-24 00:00:00'),
tablec_p20250324 FOR VALUES FROM ('2025-03-24 00:00:00') TO
('2025-03-25 00:00:00'),
tablec_p20250325 FOR VALUES FROM ('2025-03-25 00:00:00') TO
('2025-03-26 00:00:00'),
tablec_p20250326 FOR VALUES FROM ('2025-03-26 00:00:00') TO
('2025-03-27 00:00:00'),
tablec_p20250327 FOR VALUES FROM ('2025-03-27 00:00:00') TO
('2025-03-28 00:00:00'),
tablec_p20250328 FOR VALUES FROM ('2025-03-28 00:00:00') TO
('2025-03-29 00:00:00'),
tablec_p20250329 FOR VALUES FROM ('2025-03-29 00:00:00') TO
('2025-03-30 00:00:00'),
tablec_p20250330 FOR VALUES FROM ('2025-03-30 00:00:00') TO
('2025-03-31 00:00:00'),
tablec_p20250331 FOR VALUES FROM ('2025-03-31 00:00:00') TO
('2025-04-01 00:00:00'),
tablec_p20250401 FOR VALUES FROM ('2025-04-01 00:00:00') TO
('2025-04-02 00:00:00'),
tablec_p20250402 FOR VALUES FROM ('2025-04-02 00:00:00') TO
('2025-04-03 00:00:00'),
tablec_p20250403 FOR VALUES FROM ('2025-04-03 00:00:00') TO
('2025-04-04 00:00:00'),
tablec_p20250404 FOR VALUES FROM ('2025-04-04 00:00:00') TO
('2025-04-05 00:00:00'),
tablec_p20250405 FOR VALUES FROM ('2025-04-05 00:00:00') TO
('2025-04-06 00:00:00'),
tablec_p20250406 FOR VALUES FROM ('2025-04-06 00:00:00') TO
('2025-04-07 00:00:00'),
tablec_p20250407 FOR VALUES FROM ('2025-04-07 00:00:00') TO
('2025-04-08 00:00:00'),
tablec_p20250408 FOR VALUES FROM ('2025-04-08 00:00:00') TO
('2025-04-09 00:00:00'),
tablec_p20250409 FOR VALUES FROM ('2025-04-09 00:00:00') TO
('2025-04-10 00:00:00'),
tablec_p20250410 FOR VALUES FROM ('2025-04-10 00:00:00') TO
('2025-04-11 00:00:00'),
tablec_p20250411 FOR VALUES FROM ('2025-04-11 00:00:00') TO
('2025-04-12 00:00:00'),
tablec_p20250412 FOR VALUES FROM ('2025-04-12 00:00:00') TO
('2025-04-13 00:00:00'),
tablec_p20250413 FOR VALUES FROM ('2025-04-13 00:00:00') TO
('2025-04-14 00:00:00'),
tablec_p20250414 FOR VALUES FROM ('2025-04-14 00:00:00') TO
('2025-04-15 00:00:00'),
tablec_p20250415 FOR VALUES FROM ('2025-04-15 00:00:00') TO
('2025-04-16 00:00:00'),
tablec_p20250416 FOR VALUES FROM ('2025-04-16 00:00:00') TO
('2025-04-17 00:00:00')
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------
Sort (cost=9054.03..9107.10 rows=21225 width=980) (actual
time=388717.679..388723.642 rows=11 loops=1)
Sort Key: cc.ccc_id
Sort Method: quicksort Memory: 45kB
Buffers: shared hit=19406236 read=1583894 dirtied=9 written=4190
I/O Timings: read=36703.795 write=58.618
-> Merge Join (cost=112.75..2174.05 rows=21225 width=980) (actual
time=388713.431..388719.421 rows=11 loops=1)
Merge Cond: (cc.sss_id = aa.mmm_id)
Buffers: shared hit=19406236 read=1583894 dirtied=9 written=4190
I/O Timings: read=36703.795 write=58.618
-> Merge Append (cost=7.99..16975829.82 rows=97820562 width=903)
(actual time=7.886..383344.932 rows=96215798 loops=1)
Sort Key: cc.ind2
Buffers: shared hit=19406200 read=1583893 dirtied=9 written=4190
I/O Timings: read=36703.274 write=58.618
-> Index Scan using tablec_p1970_sss_id_idx on tablec_p1970
cc_1 (cost=0.12..2.34 rows=1 width=532) (actual time=0.006..0.007 rows=0
loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250318_sss_id_idx on
tablec_p20250318 cc_2 (cost=0.29..24517.70 rows=158631 width=861) (actual
time=0.2
19..407.243 rows=15455 loops=1)
Buffers: shared hit=3229 read=262
I/O Timings: read=38.162
-> Index Scan using tablec_p20250319_sss_id_idx on
tablec_p20250319 cc_3 (cost=0.29..24929.48 rows=157041 width=861) (actual
time=0.0
08..414.683 rows=16361 loops=1)
Buffers: shared hit=3498 read=222
I/O Timings: read=24.644
-> Index Scan using tablec_p20250320_sss_id_idx on
tablec_p20250320 cc_4 (cost=0.29..26183.42 rows=162959 width=868) (actual
time=0.3
58..386.108 rows=18463 loops=1)
Buffers: shared hit=3797 read=221
I/O Timings: read=29.677
-> Index Scan using tablec_p20250321_sss_id_idx on
tablec_p20250321 cc_5 (cost=0.29..25258.03 rows=158288 width=866) (actual
time=0.2
47..422.228 rows=17652 loops=1)
Buffers: shared hit=3649 read=258
I/O Timings: read=37.714
-> Index Scan using tablec_p20250322_sss_id_idx on
tablec_p20250322 cc_6 (cost=0.42..26301.22 rows=185312 width=837) (actual
time=0.0
07..88.273 rows=3582 loops=1)
Buffers: shared hit=680 read=19
I/O Timings: read=2.968
-> Index Scan using tablec_p20250323_sss_id_idx on
tablec_p20250323 cc_7 (cost=0.42..60137.20 rows=370927 width=918) (actual
time=0.7
50..652.735 rows=19659 loops=1)
Buffers: shared hit=3832 read=263
I/O Timings: read=42.237
-> Index Scan using tablec_p20250324_sss_id_idx on
tablec_p20250324 cc_8 (cost=0.29..24645.04 rows=153782 width=862) (actual
time=0.6
42..442.535 rows=18488 loops=1)
Buffers: shared hit=3530 read=328
I/O Timings: read=41.069
-> Index Scan using tablec_p20250325_sss_id_idx on
tablec_p20250325 cc_9 (cost=0.29..22930.03 rows=144129 width=854) (actual
time=0.7
57..355.577 rows=15002 loops=1)
Buffers: shared hit=2908 read=195
I/O Timings: read=25.421
-> Index Scan using tablec_p20250326_sss_id_idx on
tablec_p20250326 cc_10 (cost=0.29..22642.78 rows=144273 width=847) (actual
time=0.
542..397.982 rows=16996 loops=1)
Buffers: shared hit=3342 read=307
I/O Timings: read=39.785
-> Index Scan using tablec_p20250327_sss_id_idx on
tablec_p20250327 cc_11 (cost=0.29..23007.96 rows=143080 width=848) (actual
time=0.
479..333.069 rows=18090 loops=1)
Buffers: shared hit=3499 read=290
I/O Timings: read=27.177
-> Index Scan using tablec_p20250328_sss_id_idx on
tablec_p20250328 cc_12 (cost=0.44..3027818.84 rows=21053820 width=904) (actual
tim
e=0.010..51767.500 rows=21045366 loops=1)
Buffers: shared hit=3891912 read=625578 written=2452
I/O Timings: read=15198.004 write=31.926
-> Index Scan using tablec_p20250329_sss_id_idx on
tablec_p20250329 cc_13 (cost=0.44..4542850.00 rows=31569066 width=908) (actual
tim
e=0.493..34196.241 rows=31570870 loops=1)
Buffers: shared hit=6181410 read=624887 written=745
I/O Timings: read=2279.724 write=13.038
-> Index Scan using tablec_p20250330_sss_id_idx on
tablec_p20250330 cc_14 (cost=0.44..4343188.38 rows=30268060 width=902) (actual
tim
e=0.009..204102.864 rows=30298328 loops=1)
Buffers: shared hit=6575789 read=225742 written=236
I/O Timings: read=13574.608 write=3.893
-> Index Scan using tablec_p20250331_sss_id_idx on
tablec_p20250331 cc_15 (cost=0.43..1757701.95 rows=12160953 width=902) (actual
tim
e=0.982..78204.583 rows=12177280 loops=1)
Buffers: shared hit=2533546 read=99131 written=757
I/O Timings: read=5231.356 write=9.761
-> Index Scan using tablec_p20250401_sss_id_idx on
tablec_p20250401 cc_16 (cost=0.42..134605.16 rows=990226 width=847) (actual
time=0
.008..2424.281 rows=964217 loops=1)
Buffers: shared hit=191564 read=6190 dirtied=9
I/O Timings: read=110.728
-> Index Scan using tablec_p20250402_sss_id_idx on
tablec_p20250402 cc_17 (cost=0.12..2.34 rows=1 width=532) (actual
time=0.003..0.00
3 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250403_sss_id_idx on
tablec_p20250403 cc_18 (cost=0.12..2.34 rows=1 width=532) (actual
time=0.002..0.00
2 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250404_sss_id_idx on
tablec_p20250404 cc_19 (cost=0.12..2.34 rows=1 width=532) (actual
time=0.247..0.24
7 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250405_sss_id_idx on
tablec_p20250405 cc_20 (cost=0.12..2.34 rows=1 width=532) (actual
time=0.002..0.00
2 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250406_sss_id_idx on
tablec_p20250406 cc_21 (cost=0.12..2.34 rows=1 width=532) (actual
time=0.442..0.44
5 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250407_sss_id_idx on
tablec_p20250407 cc_22 (cost=0.12..2.34 rows=1 width=532) (actual
time=0.002..0.00
2 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250408_sss_id_idx on
tablec_p20250408 cc_23 (cost=0.12..2.34 rows=1 width=532) (actual
time=0.002..0.00
2 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250409_sss_id_idx on
tablec_p20250409 cc_24 (cost=0.12..2.34 rows=1 width=532) (actual
time=0.433..0.43
3 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250410_sss_id_idx on
tablec_p20250410 cc_25 (cost=0.12..2.34 rows=1 width=532) (actual
time=0.002..0.00
2 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250411_sss_id_idx on
tablec_p20250411 cc_26 (cost=0.12..2.34 rows=1 width=532) (actual
time=0.002..0.00
3 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250412_sss_id_idx on
tablec_p20250412 cc_27 (cost=0.12..2.34 rows=1 width=532) (actual
time=0.252..0.25
2 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250413_sss_id_idx on
tablec_p20250413 cc_28 (cost=0.12..2.34 rows=1 width=532) (actual
time=0.333..0.33
3 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250414_sss_id_idx on
tablec_p20250414 cc_29 (cost=0.12..2.34 rows=1 width=532) (actual
time=0.323..0.32
4 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250415_sss_id_idx on
tablec_p20250415 cc_30 (cost=0.12..2.34 rows=1 width=532) (actual
time=0.315..0.67
8 rows=0 loops=1)
Buffers: shared hit=1
-> Sort (cost=56.94..57.01 rows=30 width=61) (actual
time=7.127..12.496 rows=1 loops=1)
Sort Key: aa.mmm_id
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=36 read=1
I/O Timings: read=0.521
-> Nested Loop (cost=0.72..56.20 rows=30 width=61) (actual
time=6.216..12.488 rows=1 loops=1)
Buffers: shared hit=36 read=1
I/O Timings: read=0.521
-> Append (cost=0.29..40.09 rows=30 width=28) (actual
time=5.704..11.974 rows=1 loops=1)
Buffers: shared hit=32 read=1
I/O Timings: read=0.521
-> Index Scan using tablea_p1970_mmm_sss_id_idx on
tablea_p1970 aa_1 (cost=0.29..2.51 rows=1 width=
28) (actual time=0.921..0.922 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250318_mmm_sss_id_idx
on tablea_p20250318 aa_2 (cost=0.28..2.50 rows=
1 width=28) (actual time=0.005..0.010 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250319_mmm_sss_id_idx
on tablea_p20250319 aa_3 (cost=0.28..2.50 rows=
1 width=28) (actual time=0.004..0.250 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250320_mmm_sss_id_idx
on tablea_p20250320 aa_4 (cost=0.28..2.50 rows=
1 width=28) (actual time=0.430..0.431 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250321_mmm_sss_id_idx
on tablea_p20250321 aa_5 (cost=0.28..2.50 rows=
1 width=28) (actual time=0.004..0.006 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250322_mmm_sss_id_idx
on tablea_p20250322 aa_6 (cost=0.28..2.50 rows=
1 width=28) (actual time=0.004..0.271 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250323_mmm_sss_id_idx
on tablea_p20250323 aa_7 (cost=0.28..2.50 rows=
1 width=28) (actual time=0.004..0.005 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250324_mmm_sss_id_idx
on tablea_p20250324 aa_8 (cost=0.28..2.50 rows=
1 width=28) (actual time=0.505..0.857 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250325_mmm_sss_id_idx
on tablea_p20250325 aa_9 (cost=0.28..2.50 rows=
1 width=28) (actual time=0.430..0.431 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250326_mmm_sss_id_idx
on tablea_p20250326 aa_10 (cost=0.28..2.50 rows
=1 width=28) (actual time=0.368..0.820 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250327_mmm_sss_id_idx
on tablea_p20250327 aa_11 (cost=0.28..2.50 rows
=1 width=28) (actual time=0.662..0.664 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250328_mmm_sss_id_idx
on tablea_p20250328 aa_12 (cost=0.28..2.50 rows
=1 width=28) (actual time=0.310..0.311 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250329_mmm_sss_id_idx
on tablea_p20250329 aa_13 (cost=0.28..2.50 rows
=1 width=28) (actual time=0.424..0.712 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250330_mmm_sss_id_idx
on tablea_p20250330 aa_14 (cost=0.28..2.49 rows
=1 width=28) (actual time=0.682..0.683 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250331_mmm_sss_id_idx
on tablea_p20250331 aa_15 (cost=0.27..2.49 rows
=1 width=28) (actual time=0.943..1.262 rows=1 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2 read=1
I/O Timings: read=0.521
-> Index Scan using tablea_p20250401_mmm_sss_id_idx
on tablea_p20250401 aa_16 (cost=0.27..2.49 rows
=1 width=28) (actual time=0.414..0.414 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Seq Scan on tablea_p20250402 aa_17
(cost=0.00..0.00 rows=1 width=28) (actual time=0.005..0.005 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250403 aa_18
(cost=0.00..0.00 rows=1 width=28) (actual time=0.266..0.267 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250404 aa_19
(cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250405 aa_20
(cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250406 aa_21
(cost=0.00..0.00 rows=1 width=28) (actual time=0.153..0.507 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250407 aa_22
(cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250408 aa_23
(cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250409 aa_24
(cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.003 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250410 aa_25
(cost=0.00..0.00 rows=1 width=28) (actual time=0.042..0.042 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250411 aa_26
(cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250412 aa_27
(cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.066 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250413 aa_28
(cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250414 aa_29
(cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250415 aa_30
(cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Memoize (cost=0.43..2.65 rows=1 width=41) (actual
time=0.509..0.510 rows=1 loops=1)
Cache Key: aa.sss_id
Cache Mode: logical
Hits: 0 Misses: 1 Evictions: 0 Overflows: 0
Memory Usage: 1kB
Buffers: shared hit=4
-> Index Scan using tableb_pkey on tableb bb
(cost=0.42..2.64 rows=1 width=41) (actual time=0.504..0.504 rows=1 loops=1)
Index Cond: (sss_id = aa.sss_id)
Buffers: shared hit=4
Planning Time: 7.588 ms
Execution Time: 388786.455 ms
(186 rows)
