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?.
create table t1(id int,name text);

 
CREATE TABLE t1_name_null
(
 CONSTRAINT null_check CHECK (name IS NULL)
) INHERITS (t1);


CREATE or replace FUNCTION func_t1_insert_trigger()
 RETURNS trigger
 LANGUAGE 'plpgsql'
 COST 100
 VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
 chk_cond text;
 c_table TEXT;
 c_table1 text;
 new_name  text;
 m_table1 text;
 BEGIN
 
 if ( NEW.name is null) THEN
 INSERT into t1_name_null VALUES (NEW.*);
 elseif ( NEW.name is not null) THEN
 new_name:= substr(NEW.name,1,1);
 raise info 'new_name %',new_name;
 
 c_table := TG_TABLE_NAME || '_' || new_name;
 c_table1 := '' || c_table;
 m_table1 := ''||TG_TABLE_NAME;
 IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=lower(c_table)) THEN
 RAISE NOTICE 'values out of range partition, creating partition table: %',c_table;

 chk_cond := new_name||'%'; 
 raise info 'chk_cond %',chk_cond;
 EXECUTE 'CREATE TABLE ' || c_table || '(check ( name like '''|| chk_cond||''')) INHERITS (' ||TG_TABLE_NAME|| ');';

END IF;
 
 EXECUTE 'INSERT INTO ' || c_table1 || ' SELECT(' || m_table1 || ' ' || quote_literal(NEW) || ').* RETURNING id;';
 END IF;
 RETURN NULL;
 END;
$BODY$;

CREATE TRIGGER t1_trigger
 BEFORE INSERT OR UPDATE
 ON t1
 FOR EACH ROW
 EXECUTE PROCEDURE func_t1_insert_trigger()
 
 insert into t1(id,name) values(1,'Jyothi');
  insert into t1(id,name) values(2,'NagaJyothi');
   insert into t1(id,name) values(7,'Madhu');
    insert into t1(id,name) values(3,'Raj');
	 insert into t1(id,name) values(5,'Arin');
	  insert into t1(id,name) values(8,'Ravi');
	   insert into t1(id,name) values(4,'Vamshi');
	    insert into t1(id,name) values(5,'india');
		 insert into t1(id,name) values(9,'USA');
		  insert into t1(id,name) values(12,'Kiran');
		   insert into t1(id,name) values(11,null);
explain analyze select * from t1 where name = 'Arin';


QUERY PLAN


Append  (cost=0.00..233.15 rows=55 width=36) (actual time=0.050..0.091 rows=1 loops=1)

  ->  Seq Scan on t1  (cost=0.00..0.00 rows=1 width=36) (actual time=0.006..0.007 rows=0 loops=1)

        Filter: (name = 'Arin'::text)

  ->  Seq Scan on t1_j  (cost=0.00..25.88 rows=6 width=36) (actual time=0.011..0.011 rows=0 loops=1)

        Filter: (name = 'Arin'::text)

        Rows Removed by Filter: 1

  ->  Seq Scan on t1_n  (cost=0.00..25.88 rows=6 width=36) (actual time=0.013..0.013 rows=0 loops=1)

        Filter: (name = 'Arin'::text)

        Rows Removed by Filter: 1

  ->  Seq Scan on t1_m  (cost=0.00..25.88 rows=6 width=36) (actual time=0.006..0.006 rows=0 loops=1)

        Filter: (name = 'Arin'::text)

        Rows Removed by Filter: 1

  ->  Seq Scan on t1_r  (cost=0.00..25.88 rows=6 width=36) (actual time=0.006..0.006 rows=0 loops=1)

        Filter: (name = 'Arin'::text)

        Rows Removed by Filter: 2

  ->  Seq Scan on t1_a  (cost=0.00..25.88 rows=6 width=36) (actual time=0.006..0.006 rows=1 loops=1)

        Filter: (name = 'Arin'::text)

  ->  Seq Scan on t1_v  (cost=0.00..25.88 rows=6 width=36) (actual time=0.006..0.006 rows=0 loops=1)

        Filter: (name = 'Arin'::text)

        Rows Removed by Filter: 1

  ->  Seq Scan on t1_i  (cost=0.00..25.88 rows=6 width=36) (actual time=0.019..0.020 rows=0 loops=1)

        Filter: (name = 'Arin'::text)

        Rows Removed by Filter: 1

  ->  Seq Scan on t1_u  (cost=0.00..25.88 rows=6 width=36) (actual time=0.006..0.007 rows=0 loops=1)

        Filter: (name = 'Arin'::text)

        Rows Removed by Filter: 1

  ->  Seq Scan on t1_k  (cost=0.00..25.88 rows=6 width=36) (actual time=0.006..0.006 rows=0 loops=1)

        Filter: (name = 'Arin'::text)

        Rows Removed by Filter: 1

Planning Time: 0.743 ms

Execution Time: 0.131 ms


explain analyze  select * from t1 where name like 'A%';


QUERY PLAN


Append  (cost=0.00..233.15 rows=55 width=36) (actual time=0.037..0.061 rows=1 loops=1)

  ->  Seq Scan on t1  (cost=0.00..0.00 rows=1 width=36) (actual time=0.005..0.005 rows=0 loops=1)

        Filter: (name ~~ 'A%'::text)

  ->  Seq Scan on t1_j  (cost=0.00..25.88 rows=6 width=36) (actual time=0.008..0.008 rows=0 loops=1)

        Filter: (name ~~ 'A%'::text)

        Rows Removed by Filter: 1

  ->  Seq Scan on t1_n  (cost=0.00..25.88 rows=6 width=36) (actual time=0.006..0.006 rows=0 loops=1)

        Filter: (name ~~ 'A%'::text)

        Rows Removed by Filter: 1

  ->  Seq Scan on t1_m  (cost=0.00..25.88 rows=6 width=36) (actual time=0.005..0.005 rows=0 loops=1)

        Filter: (name ~~ 'A%'::text)

        Rows Removed by Filter: 1

  ->  Seq Scan on t1_r  (cost=0.00..25.88 rows=6 width=36) (actual time=0.006..0.006 rows=0 loops=1)

        Filter: (name ~~ 'A%'::text)

        Rows Removed by Filter: 2

  ->  Seq Scan on t1_a  (cost=0.00..25.88 rows=6 width=36) (actual time=0.005..0.005 rows=1 loops=1)

        Filter: (name ~~ 'A%'::text)

  ->  Seq Scan on t1_v  (cost=0.00..25.88 rows=6 width=36) (actual time=0.006..0.006 rows=0 loops=1)

        Filter: (name ~~ 'A%'::text)

        Rows Removed by Filter: 1

  ->  Seq Scan on t1_i  (cost=0.00..25.88 rows=6 width=36) (actual time=0.005..0.005 rows=0 loops=1)

        Filter: (name ~~ 'A%'::text)

        Rows Removed by Filter: 1

  ->  Seq Scan on t1_u  (cost=0.00..25.88 rows=6 width=36) (actual time=0.005..0.005 rows=0 loops=1)

        Filter: (name ~~ 'A%'::text)

        Rows Removed by Filter: 1

  ->  Seq Scan on t1_k  (cost=0.00..25.88 rows=6 width=36) (actual time=0.005..0.005 rows=0 loops=1)

        Filter: (name ~~ 'A%'::text)

        Rows Removed by Filter: 1

Planning Time: 0.206 ms

Execution Time: 0.117 ms