Postgres 15

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 schema perf;
create table perf.dba_hist_sqltext(
  queryid bigint primary key
, query	text);

CREATE SCHEMA



CREATE TABLE


create table pg_stat_statements(queryid,query)as values
(1,'select*from everything join all_else on true;');

SELECT 1


WITH uniqstatements AS (
    SELECT ss.queryid,
           min(ss.query) query
    FROM pg_stat_statements AS ss
    GROUP by ss.queryid )
INSERT INTO perf.dba_hist_sqltext 
SELECT ss.queryid,
       ss.query
FROM uniqstatements AS ss
WHERE NOT EXISTS (
      SELECT 'X'
      FROM perf.dba_hist_sqltext AS st
      WHERE st.queryid = ss.queryid)
RETURNING *;


queryid
query


1
select*from everything join all_else on true;



INSERT 0 1


WITH uniqstatements AS (
    SELECT ss.queryid,
           min(ss.query) query
    FROM pg_stat_statements AS ss
    GROUP by ss.queryid )
INSERT INTO perf.dba_hist_sqltext 
SELECT ss.queryid,
       ss.query
FROM uniqstatements AS ss
WHERE NOT EXISTS (
      SELECT 'X'
      FROM perf.dba_hist_sqltext AS st
      WHERE st.queryid = ss.queryid)
RETURNING *;


queryid
query



INSERT 0 0