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||'%';
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 |