Delete tables difference involves seq scan - Mailing list pgsql-performance

Hello. I want to remove rows from first table, that exist in second (equality is done using PK). However I experience seq scan on second table, which counters my intuition - I think it should be index-only. Because tables are large, performance of query is very bad. However I got mixed results when trying to reproduce this behavior on syntetic tables. Here I'll show 3 different plans, which I got for the same query. 1. Setup is: --------------------------- create table diff (id uuid constraint diff_pkey primary key); create table origin (id uuid constraint origin_pkey primary key); --------------------------- The query generates correct plan, which performs only index scans: explain delete from origin where exists (select id from diff where origin.id = diff.id); QUERY PLAN ------------------------------------------------------------------------------------------- Delete on origin (cost=0.30..105.56 rows=1850 width=12) -> Merge Semi Join (cost=0.30..105.56 rows=1850 width=12) Merge Cond: (origin.id = diff.id) -> Index Scan using origin_pkey on origin (cost=0.15..38.90 rows=1850 width=22) -> Index Scan using diff_pkey on diff (cost=0.15..38.90 rows=1850 width=22) (5 rows) 2. Setup is: -------------------------------- create table origin (id uuid constraint origin_pkey primary key, data jsonb); create table diff (id uuid constraint diff_pkey primary key, data jsonb); -------------------------------- The query generates plan with two seq scans: explain delete from origin where exists (select id from diff where origin.id = diff.id); QUERY PLAN --------------------------------------------------------------------------- Delete on origin (cost=34.08..69.49 rows=1070 width=12) -> Hash Semi Join (cost=34.08..69.49 rows=1070 width=12) Hash Cond: (origin.id = diff.id) -> Seq Scan on origin (cost=0.00..20.70 rows=1070 width=22) -> Hash (cost=20.70..20.70 rows=1070 width=22) -> Seq Scan on diff (cost=0.00..20.70 rows=1070 width=22) (6 rows) 3. My real `origin` table has 26 fields and 800 billion rows, real `diff` table has 12 million rows and the query generates plan with nested loop and seq scan on `diff` table: explain delete from drug_refills origin where exists (select id from drug_refills_diff diff where origin.id = diff.id); QUERY PLAN ---------------------------------------------------------------------------------------------------------- Delete on drug_refills origin (cost=0.57..22049570.11 rows=11975161 width=12) -> Nested Loop (cost=0.57..22049570.11 rows=11975161 width=12) -> Seq Scan on drug_refills_diff diff (cost=0.00..720405.61 rows=11975161 width=22) -> Index Scan using drug_refills_pkey on drug_refills origin (cost=0.57..1.77 rows=1 width=22) Index Cond: (id = diff.id) (5 rows) I have run ANALYZE on both tables, but it didn't help. Here are column types in origin and diff (same schema), if that matters: uuid timestamp with time zone timestamp with time zone character varying(255) character varying(255) character varying(1024) numeric(10,4) integer numeric(14,8) numeric(14,8) numeric(14,8) numeric(14,8) numeric(14,8) character varying(16) character varying(16) character varying(16) character varying(16) character varying(16) character varying(16) date jsonb text[] uuid uuid uuid uuid