Consider these 3 tables
Members
CREATE TABLE public.members
(
id integer NOT NULL DEFAULT nextval('members_id_seq'::regclass),
client_id integer,
login character varying(255) COLLATE pg_catalog."default" NOT NULL,
password character varying(255) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT members_pkey PRIMARY KEY (id),
CONSTRAINT fk_45a0d2ff19eb6921 FOREIGN KEY (client_id)
REFERENCES public.clients (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE CASCADE
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
CREATE INDEX idx_45a0d2ff19eb6921
ON public.members USING btree
(client_id)
TABLESPACE pg_default;
Clients
CREATE TABLE public.clients
(
id integer NOT NULL DEFAULT nextval('clients_id_seq'::regclass),
name character varying(255) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT clients_pkey PRIMARY KEY (id)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
Invoices
CREATE TABLE public.invoices
(
id integer NOT NULL DEFAULT nextval('invoices_id_seq'::regclass),
client_id integer,
member_id integer,
CONSTRAINT invoices_pkey PRIMARY KEY (id),
CONSTRAINT fk_6a2f2f9519eb6921 FOREIGN KEY (client_id)
REFERENCES public.clients (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE SET NULL,
CONSTRAINT fk_6a2f2f957597d3fe FOREIGN KEY (member_id)
REFERENCES public.members (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE SET NULL
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
CREATE INDEX idx_6a2f2f9519eb6921
ON public.invoices USING btree
(client_id)
TABLESPACE pg_default;
CREATE INDEX idx_6a2f2f957597d3fe
ON public.invoices USING btree
(member_id)
TABLESPACE pg_default;
I tried to execute the simple query below on several environment
SELECT i.*, m.id AS member_id FROM invoices i
LEFT JOIN members m ON m.id = i.member_id
WHERE i.member_id is null;
On DEBIAN 7 - 8, postgresql 9.6 > works great
On DEBIAN 7 - 8, postgresql 10.5 > works great
On MacOS 10.13, postgresql 9.6 > works great
On MacOS 10.13, postgresql 10.1 > Execution time is 4min
On MacOS 10.13, postgresql 10.5 (dev env) > Execution time is 4min
I don't understand why this simple query take a huge time on my dev machine. There are ~140000 invoices and this query should display ~13000 records.
Edit
As asked, this is the result of EXPLAIN(analyze, buffers) on my dev env (Mac os - postgresql 10.5) :
Nested Loop Left Join (cost=9.41..298.20 rows=81 width=1177) (actual time=117.131..366691.852 rows=13429 loops=1)
Join Filter: (m.id = i.member_id)
Rows Removed by Join Filter: 1844365718
Buffers: shared hit=127505, temp read=3169008 written=235
-> Bitmap Heap Scan on invoices i (cost=9.04..288.60 rows=81 width=1173) (actual time=1.178..13.076 rows=13429 loops=1)
Recheck Cond: (member_id IS NULL)
Heap Blocks: exact=651
Buffers: shared hit=691
-> Bitmap Index Scan on idx_6a2f2f957597d3fe (cost=0.00..9.02 rows=81 width=0) (actual time=1.054..1.054 rows=13429 loops=1)
Index Cond: (member_id IS NULL)
Buffers: shared hit=40
-> Materialize (cost=0.38..8.39 rows=1 width=4) (actual time=0.005..15.093 rows=137342 loops=13429)
Buffers: shared hit=126814, temp read=3169008 written=235
-> Index Only Scan using members_pkey on members m (cost=0.38..8.39 rows=1 width=4) (actual time=0.082..67.872 rows=137342 loops=1)
Heap Fetches: 137342
Buffers: shared hit=126814
Planning time: 0.341 ms
Execution time: 366695.678 ms
And the same on Debian 8 - postgresql 10.5 :
rows=13774 loops=1)
Hash Cond: (i.member_id = m.id)
Buffers: shared hit=1089 read=4461, temp written=202
-> Bitmap Heap Scan on invoices i (cost=258.39..3122.13 rows=13674 width=732) (actual time=3.299..5.702 rows=13774 loops=1)
Recheck Cond: (member_id IS NULL)
Heap Blocks: exact=661
Buffers: shared hit=702
-> Bitmap Index Scan on idx_6a2f2f957597d3fe (cost=0.00..254.97 rows=13674 width=0) (actual time=2.928..2.928 rows=13774 loops=1)
Index Cond: (member_id IS NULL)
Buffers: shared hit=41
-> Hash (cost=6219.50..6219.50 rows=137450 width=4) (actual time=113.745..113.745 rows=137450 loops=1)
Buckets: 131072 Batches: 2 Memory Usage: 3447kB
Buffers: shared hit=384 read=4461, temp written=201
-> Seq Scan on members m (cost=0.00..6219.50 rows=137450 width=4) (actual time=0.056..54.524 rows=137450 loops=1)
Buffers: shared hit=384 read=4461
Planning time: 3.456 ms
Execution time: 126.951 ms
Any idea will be very appreciated
explain (analyze, buffers)
(not just a simple "explain"). Formatted text please, no screen shotswork_mem
on both servers? (show work_mem;
will tell you). Additionally: the estimates on the Mac installation seem to be way off. Does runninganalyze
for all involved tables change anything?show work_mem;
is4mb
for both env. But since I launchAnalyze
, this query is correctly executed ??? Good news, but do you have an explanation ?