Postgresql avoid nested loop with join

Question

Please help me to improve query performance if possible.

I have following query

    select
        s."CustomerCode",
        s."MaterialCode",
        fw."Name",
        fw."ReverseName",
        s."Uc"
    from
        "Sales" s
    left join
        "FiscalWeeks" fw on s."SalesDate" between fw."StartedAt" and fw."EndedAt"

And execution plan is

"Nested Loop Left Join  (cost=0.00..1439970.46 rows=8954562 width=40) (actual time=0.129..114889.581 rows=1492427 loops=1)"
"  Join Filter: ((s."SalesDate" >= fw."StartedAt") AND (s."SalesDate" <= fw."EndedAt"))"
"  Rows Removed by Join Filter: 79098631"
"  Buffers: shared hit=3818 read=10884"
"  ->  Seq Scan on "Sales" s  (cost=0.00..29625.27 rows=1492427 width=26) (actual time=0.098..1216.287 rows=1492427 loops=1)"
"        Buffers: shared hit=3817 read=10884"
"  ->  Materialize  (cost=0.00..1.81 rows=54 width=26) (actual time=0.001..0.034 rows=54 loops=1492427)"
"        Buffers: shared hit=1"
"        ->  Seq Scan on "FiscalWeeks" fw  (cost=0.00..1.54 rows=54 width=26) (actual time=0.006..0.044 rows=54 loops=1)"
"              Buffers: shared hit=1"
"Planning time: 0.291 ms"
"Execution time: 115840.838 ms"

I have following indexes

CREATE INDEX "Sales_SalesDate_idx" ON public."Sales" USING btree ("SalesDate");
ADD CONSTRAINT "FiscalWeekUnique" EXCLUDE USING gist (daterange("StartedAt", "EndedAt", '[]'::text) WITH &&);

Postgresql version is

"PostgreSQL 9.5.0, compiled by Visual C++ build 1800, 32-bit"

vacuum analyze was performed

I think that postgresql does not understand that for each row in Sales table exists only one row in table FiscalWeeks and use nested loop. How can I explain it?

Thank you.

Laurenz Albe · Accepted Answer · 2017-10-02 08:28:50Z

3

The query has to use a nested loop join because of the join condition. The operators <= and >= do not support hash or merge joins.

Perhaps you can improve the query by adding an index to "FiscalWeeks" so that a sequential scan can be avoided, and the join condition can be pushed down into the inner loop:

CREATE INDEX ON "FiscalWeeks" ("StartedAt", "EndedAt");

Unrelated to that, but you would make your life better if you avoided upper case letters in table and column names.

answered Oct 2, 2017 at 8:28

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nikita Bannikov Over a year ago

Ok, thank you for advice. I decided to change table FiscalWeeks to table Days which contains column with all days of year and FiscalWeekName. Now I join tables together with operator =.

Collectives™ on Stack Overflow

Postgresql avoid nested loop with join

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related