Joining 16 tables seems slow

From: "Frank Millman" <frank(at)chagford(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Joining 16 tables seems slow
Date: 2017-09-12 06:45:58
Message-ID: A6E3B63173EC4D3897C9F06B85319E22@FrankLaptop
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all

I am using 9.4.4 on Fedora 22.

I am experimenting with optimising a SQL statement. One version uses 4 LEFT JOIN’s and a 5-way CASE statement in the body. The second moves the filtering into the JOIN section, and I end up with 16 LEFT JOIN’s and no CASE statements.

My test involves selecting a single row. Both versions work. The first version takes 0.06 seconds. The second takes 0.23 seconds. On further experimentation, the time for the second one seems to taken in setting up the joins, because if I omit selecting anything from the joined tables, it still takes 0.23 seconds.

Exactly the same exercise on Sql Server results in 0.06 seconds for both versions.

I realise that, if I was selecting a large number of rows, 0.23 seconds is trivial and the overall result could be different. But still, it seems odd.

Is this normal, or should I investigate further?

Frank Millman

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2017-09-12 06:53:12 Re: Joining 16 tables seems slow
Previous Message Yogesh Sharma 2017-09-12 01:02:16 Perl script is killed by SIGPIPE