Postgres 13

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 13.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit



SELECT 1


 CREATE TABLE trade (     --                            +
     id integer NOT NULL,           --                  +
     epoq integer NOT NULL,           --                +
     prix numeric NOT NULL,           --                +
     qte numeric NOT NULL,           --                 +
     t_mon integer NOT NULL,           --               +
     t_yr integer NOT NULL,           --                +
     sym_txt text NOT NULL,           --                +
     ts timestamp with time zone NOT NULL,           -- +
     sym_id smallint NOT NULL     --                    +
 );

CREATE TABLE


CREATE OR REPLACE FUNCTION show_create_table(table_name TEXT, join_char TEXT = E'\n' ) 
  RETURNS TEXT AS 
$BODY$
SELECT 
  'CREATE TABLE ' || $1 || ' (     -- ' || $2 || '' || 
  STRING_AGG(column_list.column_expr, ',           -- ' || $2 || '') || 
    '     -- ' || $2 || ');'
FROM 
(
  SELECT 
    '    ' || column_name || ' ' || data_type || 
    COALESCE
    (
      '(' || character_maximum_length || ')', ''
    ) || 
    CASE 
      WHEN is_nullable = 'YES' then '' 
      ELSE ' NOT NULL' 
    END AS column_expr
  FROM information_schema.columns
  WHERE table_schema = 'public' AND table_name = $1
  ORDER BY ordinal_position
) column_list;
$BODY$ LANGUAGE SQL STABLE;

CREATE FUNCTION


SELECT show_create_table('trade');


show_create_table


CREATE TABLE trade (     -- 
    id integer NOT NULL,           -- 
    epoq integer NOT NULL,           -- 
    prix numeric NOT NULL,           -- 
    qte numeric NOT NULL,           -- 
    t_mon integer NOT NULL,           -- 
    t_yr integer NOT NULL,           -- 
    sym_txt text NOT NULL,           -- 
    ts timestamp with time zone NOT NULL,           -- 
    sym_id smallint NOT NULL     -- 
);



SELECT 1


--
-- Copied directly from the SELECT show_cre...(trade) result above
--

CREATE TABLE trade_bis (     --    
    id integer NOT NULL,           -- 
    epoq integer NOT NULL,           -- 
    prix numeric NOT NULL,           -- 
    qte numeric NOT NULL,           -- 
    t_mon integer NOT NULL,           -- 
    t_yr integer NOT NULL,           -- 
    sym_txt text NOT NULL,           -- 
    ts timestamp with time zone NOT NULL,           -- 
    sym_id smallint NOT NULL     -- 
);

CREATE TABLE


INSERT INTO trade_bis VALUES
(1, 1628609486, 2.36400,  29.18557000, 8, 2021, '1INCHEUR', '2021-08-10 15:31:26+01', 214),
(2, 1628609494, 2.36400,  12.50281000, 8, 2021, '1INCHEUR', '2021-08-10 15:31:34+01', 214),
(3, 1628609496, 2.36300, 416.76043000, 8, 2021, '1INCHEUR', '2021-08-10 15:31:36+01', 214),
(4, 1628609520, 2.36500,  38.94156000, 8, 2021, '1INCHEUR', '2021-08-10 15:32:00+01', 214);

INSERT 0 4


SELECT * FROM trade_bis;


id
epoq
prix
qte
t_mon
t_yr
sym_txt
ts
sym_id


1
1628609486
2.36400
29.18557000
8
2021
1INCHEUR
2021-08-10 15:31:26+01
214

2
1628609494
2.36400
12.50281000
8
2021
1INCHEUR
2021-08-10 15:31:34+01
214

3
1628609496
2.36300
416.76043000
8
2021
1INCHEUR
2021-08-10 15:31:36+01
214

4
1628609520
2.36500
38.94156000
8
2021
1INCHEUR
2021-08-10 15:32:00+01
214



SELECT 4

id	epoq	prix	qte	t_mon	t_yr	sym_txt	ts	sym_id
1	1628609486	2.36400	29.18557000	8	2021	1INCHEUR	2021-08-10 15:31:26+01	214
2	1628609494	2.36400	12.50281000	8	2021	1INCHEUR	2021-08-10 15:31:34+01	214
3	1628609496	2.36300	416.76043000	8	2021	1INCHEUR	2021-08-10 15:31:36+01	214
4	1628609520	2.36500	38.94156000	8	2021	1INCHEUR	2021-08-10 15:32:00+01	214