Hi Frank,

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

Reply via email to