Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select version();


version


PostgreSQL 11.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


CREATE TABLE transfer_order_header
(
    load_dttm timestamp with time zone NOT NULL,
    md5_checksum character varying(200) COLLATE pg_catalog."default",
    eventid character varying(200) COLLATE pg_catalog."default",
    eventtype character varying(200) COLLATE pg_catalog."default",
    i_am_unique_id character varying(2000) COLLATE pg_catalog."default",
    eventversion character varying(200) COLLATE pg_catalog."default",
    eventtime timestamp with time zone,
    eventproducerid character varying(200) COLLATE pg_catalog."default",
    sto_id character varying(200) COLLATE pg_catalog."default",
    activityid character varying(200) COLLATE pg_catalog."default",
    applicationid character varying(200) COLLATE pg_catalog."default",
    applicationuserid character varying(200) COLLATE pg_catalog."default",
    channelid character varying(200) COLLATE pg_catalog."default",
    interactionid character varying(200) COLLATE pg_catalog."default",
    sessionid character varying(200) COLLATE pg_catalog."default",
    workflow_id character varying(200) COLLATE pg_catalog."default",
    storeid character varying(200) COLLATE pg_catalog."default",
    frontend character varying(200) COLLATE pg_catalog."default",
    header_session_id character varying(200) COLLATE pg_catalog."default",
    transtype character varying(200) COLLATE pg_catalog."default",
    supplyingplant character varying(200) COLLATE pg_catalog."default",
    storagelocation character varying(200) COLLATE pg_catalog."default",
    handdeliveredflag character varying(200) COLLATE pg_catalog."default",
    postingdate date,
    trackingnumber character varying(200) COLLATE pg_catalog."default",
    boxnumber character varying(200) COLLATE pg_catalog."default",
    username character varying(200) COLLATE pg_catalog."default",
    operation_type character varying(200) COLLATE pg_catalog."default"
);

CREATE INDEX  ON transfer_order_header 
    (eventid COLLATE pg_catalog."default" ASC NULLS LAST,
    supplyingplant COLLATE pg_catalog."default" ASC NULLS LAST,
    eventtime DESC NULLS FIRST);
CREATE TABLE transfer_order_item
(
    eventid character varying(200) COLLATE pg_catalog."default",
    eventtype character varying(200) COLLATE pg_catalog."default",
    sto_id character varying(200) COLLATE pg_catalog."default",
    deliveryitem character varying(200) COLLATE pg_catalog."default",
    item_sku character varying(200) COLLATE pg_catalog."default",
    serialnumber character varying(200) COLLATE pg_catalog."default",
    sku character varying(200) COLLATE pg_catalog."default",
    program_code character varying(200) COLLATE pg_catalog."default",
    transaction_type character varying(200) COLLATE pg_catalog."default",
    receivingplant character varying(200) COLLATE pg_catalog."default",
    serializationflag character varying(200) COLLATE pg_catalog."default",
    mandatoryreturns character varying(200) COLLATE pg_catalog."default",
    quantity numeric(15,2),
    postingdate date,
    uom character varying(200) COLLATE pg_catalog."default",
    load_dttm timestamp with time zone NOT NULL,
    str character varying(200) COLLATE pg_catalog."default"
);



CREATE INDEX transfer_order_item_eventid_idx
    ON transfer_order_item USING hash
    (eventid COLLATE pg_catalog."default") ;

CREATE INDEX transfer_order_item_load_dttm_idx
    ON transfer_order_item USING btree
    (load_dttm ASC NULLS LAST);
explain (analyze,buffers)
select     COALESCE(itm.serialnumber,'') AS SERIAL_NO, 
            COALESCE(itm.ITEM_SKU,'') AS SKU, 
            COALESCE(itm.receivingplant,'') AS RECEIVINGPLANT, 
			COALESCE(itm.STO_ID,'') AS STO,
			supplyingplant,
            COALESCE(itm.deliveryitem,'') AS DELIVERYITEM,
		    min(eventtime) as eventtime 
 FROM transfer_order_header hed,transfer_order_item itm 
 where hed.eventid=itm.eventid group by 1,2,3,4,5,6
 


QUERY PLAN


GroupAggregate  (cost=16.18..16.21 rows=1 width=586) (actual time=0.034..0.035 rows=0 loops=1)

  Group Key: (COALESCE(itm.serialnumber, ''::character varying)), (COALESCE(itm.item_sku, ''::character varying)), (COALESCE(itm.receivingplant, ''::character varying)), (COALESCE(itm.sto_id, ''::character varying)), hed.supplyingplant, (COALESCE(itm.deliveryitem, ''::character varying))

  Buffers: shared hit=1

  ->  Sort  (cost=16.18..16.18 rows=1 width=586) (actual time=0.033..0.034 rows=0 loops=1)

        Sort Key: (COALESCE(itm.serialnumber, ''::character varying)), (COALESCE(itm.item_sku, ''::character varying)), (COALESCE(itm.receivingplant, ''::character varying)), (COALESCE(itm.sto_id, ''::character varying)), hed.supplyingplant, (COALESCE(itm.deliveryitem, ''::character varying))

        Sort Method: quicksort  Memory: 25kB

        Buffers: shared hit=1

        ->  Nested Loop  (cost=0.12..16.17 rows=1 width=586) (actual time=0.014..0.014 rows=0 loops=1)

              Buffers: shared hit=1

              ->  Index Only Scan using transfer_order_header_eventid_supplyingplant_eventtime_idx on transfer_order_header hed  (cost=0.12..8.14 rows=1 width=844) (actual time=0.013..0.013 rows=0 loops=1)

                    Heap Fetches: 0

                    Buffers: shared hit=1

              ->  Index Scan using transfer_order_item_eventid_idx on transfer_order_item itm  (cost=0.00..8.02 rows=1 width=2508) (never executed)

                    Index Cond: ((eventid)::text = (hed.eventid)::text)

Planning Time: 0.698 ms

Execution Time: 0.165 ms