Re: Slow SELECT

Hello

On 2020-05-26 10:38, Frank Millman wrote:
> On 2020-05-26 9:32 AM, Olivier Gautherot wrote:
>> Hi Frank,
>>
>> On Tue, May 26, 2020 at 9:23 AM Frank Millman <frank(at)chagford(dot)com
>> <mailto:frank(at)chagford(dot)com>> wrote:
>>
>> Hi all
>>
>> I have a SELECT that runs over 5 times slower on PostgreSQL
>> compared
>> with Sql Server and sqlite3. I am trying to understand why.
>>
>> I have a table that looks like this (simplified) -
>>
>> CREATE TABLE my_table (
>>      row_id SERIAL PRIMARY KEY,
>>      deleted_id INT DEFAULT 0,
>>      fld_1 INT REFERENCES table_1(row_id),
>>      fld_2 INT REFERENCES table_2(row_id),
>>      fld_3 INT REFERENCES table_3(row_id),
>>      fld_4 INT REFERENCES table_4(row_id),
>>      tran_date DATE,
>>      tran_total DEC(21,2)
>>      );
>>
>> CREATE UNIQUE INDEX my_table_ndx ON my_table (fld_1, fld_2, fld_3,
>> fld_4, tran_date) WHERE deleted_id = 0;
>>
>> The table sizes are -
>>      my_table : 167 rows
>>      table_1 : 21 rows
>>      table_2 : 11 rows
>>      table_3 : 3 rows
>>      table_4 : 16 rows
>>
>> Therefore for each tran_date in my_table there are potentially
>> 21x11x3x16 = 11088 rows. Most will be null.
>>
>> I want to select the row_id for the last tran_date for each of
>> those
>> potential groups. This is my select -
>>
>>      SELECT (
>>          SELECT a.row_id FROM my_table a
>>          WHERE a.fld_1 = b.row_id
>>          AND a.fld_2 = c.row_id
>>          AND a.fld_3 = d.row_id
>>          AND a.fld_4 = e.row_id
>>          AND a.deleted_id = 0
>>          ORDER BY a.tran_date DESC LIMIT 1
>>      )
>>      FROM table_1 b, table_2 c, table_3 d, table_4 e
>>
>> Out of 11088 rows selected, 103 are not null.
>>
>> On identical data, this takes 0.06 sec on SQL Server, 0.04 sec on
>> sqlite3, and 0.31 sec on PostgreSQL.
>>
>>
>> SQL Server does a good job at caching data in memory. PostgreSQL does
>> too on consecutive calls to the same table. What execution time do you
>> get if you issue the query a second time?
>>
>> My first guess would be to add an index on my_table.tran_date and
>> check in EXPLAIN that you don't have a SEQUENTIAL SCAN on that table.
>>
>> I have looked at the EXPLAIN, but I don't really know what to look
>> for.
>> I can supply it if that would help.
>>
>> Thanks for any advice.
>>
>
> Thanks Olivier. Unfortunately that did not help.
>
> I was already running the query twice and only timing the second one.
>
> I added the index on tran_date. The timing is the same, and EXPLAIN
> shows that it is using a SEQUENTIAL SCAN.
>
> Here is the EXPLAIN -
>
>
> Nested Loop (cost=0.00..64155.70 rows=11088 width=4)
> -> Nested Loop (cost=0.00..10.36 rows=528 width=12)
> -> Nested Loop (cost=0.00..2.56 rows=33 width=8)
> -> Seq Scan on table_2 c (cost=0.00..1.11 rows=11
> width=4)
> -> Materialize (cost=0.00..1.04 rows=3 width=4)
> -> Seq Scan on table_3 d (cost=0.00..1.03 rows=3
> width=4)
> -> Materialize (cost=0.00..1.24 rows=16 width=4)
> -> Seq Scan on table_4 e (cost=0.00..1.16 rows=16
> width=4)
> -> Materialize (cost=0.00..1.31 rows=21 width=4)
> -> Seq Scan on table_1 b (cost=0.00..1.21 rows=21 width=4)
> SubPlan 1
> -> Limit (cost=5.77..5.77 rows=1 width=8)
> -> Sort (cost=5.77..5.77 rows=1 width=8)
> Sort Key: a.tran_date DESC
> -> Seq Scan on my_table a (cost=0.00..5.76 rows=1
> width=8)
> Filter: ((fld_1 = b.row_id) AND (fld_2 =
> c.row_id) AND (fld_3 = d.row_id) AND (fld_4 = e.row_id) AND
> (deleted_id = 0))
>
>
> Frank

If I see it correct, the query runs sequential scans on all tables, i.e.
table_1 to table_4.
Do you have an index on the referenced keys (row_id) in table_1 to
table_4?

It happens often that referenced keys are not indexed, leading to poor
execution plans.

Bye
Charles