Re: Slow SELECT

On 2020-05-26 11:10, Charles Clavadetscher wrote:
> 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

I noticed later that you have very small tables. This will probably lead
to a sequential scan althought there is an index in place.

I am not sure if it makes a difference, but what about using explicit
joins?

SELECT a.row_id FROM my_table a
JOIN b table_1 ON (b.row_id = a.fld_1)
JOIN c table_2 ON (c.row_id = a.fld_2)
JOIN d table_3 ON (d.row_id = a.fld_3)
JOIN e table_4 ON (e.row_id = a.fld_4)
WHERE a.deleted_id = 0
ORDER BY a.tran_date DESC LIMIT 1;

Regards
Charles