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@xxxxxxxxxxxx
<mailto:frank@xxxxxxxxxxxx>> 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
