Re: Slow SELECT

Hi Frank,

On Tue, May 26, 2020 at 9:23 AM Frank Millman <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.
>
> Frank Millman
>
>
--
Olivier Gautherot