postgresql 10.5 slow query issue only on MacOS

Question

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

Answer 1

The execution plan shows that the estimates for the number of rows on the mac are completely off.

If the optimizer has wrong statistics about your data, it will choose the wrong execution plan.

The execution plan for retrieving data from a table that has only 1 row is greatly different to the one retrieving data from a table with a million rows.

Underestimating the number of rows a step in the execution plan returns will result in an inefficient execution plan.

On your Mac installation the optimizer thinks that the members table only contains one row and thus the optimizer chooses a plan that is efficient for just one row. But in reality the table contains 137342 rows.

The chosen "nested loop" is a lot slower for that many rows than the "Hash Join" chosen by the optimizer when the statistics are correct.

The manual explains this in more detail.

Another good place to understand execution plans is https://use-the-index-luke.com/