Thread: Slow SELECT
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.
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
Hi Frank,
On Tue, May 26, 2020 at 9:23 AM Frank Millman <frank@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
On 2020-05-26 9:32 AM, Olivier Gautherot wrote:
> Hi Frank,
>
> On Tue, May 26, 2020 at 9:23 AM Frank Millman <frank@chagford.com
> <mailto:frank@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.
>
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
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@chagford.com >> <mailto:frank@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. >> > > 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
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@chagford.com >>> <mailto:frank@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. >>> >> >> 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
On 2020-05-26 11:10 AM, 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@chagford.com >>> <mailto:frank@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. >>> >> >> 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. > The referenced keys are all defined as SERIAL PRIMARY KEY in their own tables, so I presume that that are all indexed automatically. On the other hand, there are not many rows in those tables, so the planner may decide not to use the index in that case. Frank
Hi On 26.05.20 09:22, Frank Millman wrote: > > I have looked at the EXPLAIN, but I don't really know what to look for. > I can supply it if that would help. > My favorite approach to tuning Postgres queries is: 1. Run EXPLAIN ANALYZE <query> 2. Copy/Paste the output into the fantastic https://explain.depesz.com/ This will turn the somewhat hard-to-understand explain output into a nice colored structure. If it's not obvious from the orange-reddish boxes where the slowness comes from, please post the link here and somebody will certainly have some advice. Cheers Christian -- Christian Ramseyer, netnea ag Network Management. Security. OpenSource. https://www.netnea.com
On Tue, 26 May 2020 at 19:23, Frank Millman <frank@chagford.com> wrote: > 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. Perhaps SQL Server is doing something to rewrite the subquery in the target list to a LEFT JOIN. PostgreSQL currently does not do that. Since "my_table" is small, you'd likely be much better doing a manual rewrite of the query to join a subquery containing the required details from "my_table". It looks like you want the row_id from the latest tran_date for each fld_N column. So something like: SELECT a.row_id FROM table_1 b CROSS JOIN table_2 c CROSS JOIN table_3 d CROSS JOIN table_4 e LEFT OUTER JOIN ( SELECT fld_1,fld_2,fld_3,fld_4,row_id,tran_date, ROW_NUMBER() OVER (PARTITION BY fld_1,fld_2,fld_3,fld_4 ORDER BY tran_date DESC) row_num FROM my_table WHERE deleted_id = 0 ) a ON 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.row_num = 1; Should do it. You could also perhaps do something with DISTINCT ON instead of using ROW_NUMBER(). That might be a bit more efficient, but it's unlikely to matter too much since there are only 167 rows in that table. David
On 2020-05-26 11:27 AM, Charles Clavadetscher wrote: > 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@chagford.com >>>> <mailto:frank@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. >>>> >>> >>> 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; > Thanks, Charles. I tried that, but unfortunately it produces a different result. I need to test for every possible combination of fld1-4, and get the highest date for each one. Using joins only tests existing combinations, and gets the highest date for all of them combined. Seel my reply to David Rowley. I do not fully understand his solution yet, but it seems to be what I am looking for. Thanks again Frank
On 2020-05-26 12:02 PM, Christian Ramseyer wrote: > Hi > > On 26.05.20 09:22, Frank Millman wrote: >> >> I have looked at the EXPLAIN, but I don't really know what to look for. >> I can supply it if that would help. >> > > My favorite approach to tuning Postgres queries is: > > 1. Run EXPLAIN ANALYZE <query> > 2. Copy/Paste the output into the fantastic https://explain.depesz.com/ > > This will turn the somewhat hard-to-understand explain output into a > nice colored structure. If it's not obvious from the orange-reddish > boxes where the slowness comes from, please post the link here and > somebody will certainly have some advice. > Thanks, Christian. I will definitely look into that. Frank
On 2020-05-26 12:04 PM, David Rowley wrote: > On Tue, 26 May 2020 at 19:23, Frank Millman <frank@chagford.com> wrote: >> 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. > > Perhaps SQL Server is doing something to rewrite the subquery in the > target list to a LEFT JOIN. PostgreSQL currently does not do that. > > Since "my_table" is small, you'd likely be much better doing a manual > rewrite of the query to join a subquery containing the required > details from "my_table". It looks like you want the row_id from the > latest tran_date for each fld_N column. So something like: > > SELECT a.row_id > FROM table_1 b > CROSS JOIN table_2 c > CROSS JOIN table_3 d > CROSS JOIN table_4 e > LEFT OUTER JOIN ( > SELECT fld_1,fld_2,fld_3,fld_4,row_id,tran_date, > ROW_NUMBER() OVER (PARTITION BY fld_1,fld_2,fld_3,fld_4 ORDER BY > tran_date DESC) row_num > FROM my_table > WHERE deleted_id = 0 > ) a ON 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.row_num = 1; > > Should do it. You could also perhaps do something with DISTINCT ON > instead of using ROW_NUMBER(). That might be a bit more efficient, but > it's unlikely to matter too much since there are only 167 rows in that > table. > Thank you David. I tried that and it produced the correct result in 53ms, which is what I am looking for. It will take me some time to understand it fully, so I have some homework to do! Much appreciated. Frank
On 5/26/20 12:04 PM, David Rowley wrote:
> Since "my_table" is small, you'd likely be much better doing a manual
> rewrite of the query to join a subquery containing the required
> details from "my_table". It looks like you want the row_id from the
> latest tran_date for each fld_N column. So something like:
>
> SELECT a.row_id
> FROM table_1 b
> CROSS JOIN table_2 c
> CROSS JOIN table_3 d
> CROSS JOIN table_4 e
> LEFT OUTER JOIN (
> SELECT fld_1,fld_2,fld_3,fld_4,row_id,tran_date,
> ROW_NUMBER() OVER (PARTITION BY fld_1,fld_2,fld_3,fld_4 ORDER BY
> tran_date DESC) row_num
> FROM my_table
> WHERE deleted_id = 0
> ) a ON 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.row_num = 1;
>
> Should do it. You could also perhaps do something with DISTINCT ON
> instead of using ROW_NUMBER(). That might be a bit more efficient, but
> it's unlikely to matter too much since there are only 167 rows in that
> table.
I would expect a lateral query to be better here.
SELECT a.*
FROM table_1 AS b
CROSS JOIN table_2 AS c
CROSS JOIN table_3 AS d
CROSS JOIN table_4 AS e
CROSS JOIN LATERAL (
SELECT *
FROM my_table AS a
WHERE (a.fld_1, a.fld_2, a.fld_3, a.fld_4) = (b.row_id, c.row_id,
d.row_id, e.row_id)
AND a.deleted = 0
ORDER BY a.tran_date DESC
FETCH FIRST ROW ONLY
) AS a
WHERE a.row_id IS NOT NULL;
You will likely want an index on my_table (fld_1, fld_2, fld_3, fld_4,
tran_date) if your dataset gets bigger.
This query is 100% Standard SQL, so it *should* work on other engines.
That doesn't mean it will, though.
--
Vik Fearing
On Tue, 26 May 2020 at 22:31, Frank Millman <frank@chagford.com> wrote: > Thank you David. I tried that and it produced the correct result in > 53ms, which is what I am looking for. > > It will take me some time to understand it fully, so I have some > homework to do! The main problem with your previous query was that the subquery was being executed 11088 times and could only ever find anything 167 times. The remaining number of times nothing would be found. I just changed the subquery which would be executed once per output row and altered it so it became a subquery that's joined and only executed once. The ROW_NUMBER() is a windowing function, which is explained in [1]. I used this to get the row_id of the record with the lowest tran_date, just like you were doing with the ORDER BY tran_date DESC LIMIT 1, but the subquery with the windowing function gets them all at once, rather than doing it in a way that requires it to be executed once for each row in the top-level query. In this case, the functionality that the LIMIT 1 does in your query is achieved with "AND a.row_num = 1;" in my version. This is pretty fast to execute once due to there only being 167 rows. It's also important to know that there may be cases where the method I proposed is slower. For example, if my_table was very large and contained rows that were not in table_1 to table_4. Since the subquery in my version calculates everything then it could be wasteful to do that for values that would never be used. For you, you have foreign keys that ensure my_table does not contain records that are not in the other tables, but you could still see this issue if you were to add some restrictive WHERE clause to the outer query. Perhaps this won't be a problem for you, but it's likely good to know. [1] https://www.postgresql.org/docs/current/tutorial-window.html David
On Tue, 26 May 2020 at 23:41, Vik Fearing <vik@postgresfriends.org> wrote: > > On 5/26/20 12:04 PM, David Rowley wrote: > > Since "my_table" is small, you'd likely be much better doing a manual > > rewrite of the query to join a subquery containing the required > > details from "my_table". It looks like you want the row_id from the > > latest tran_date for each fld_N column. So something like: > > > > SELECT a.row_id > > FROM table_1 b > > CROSS JOIN table_2 c > > CROSS JOIN table_3 d > > CROSS JOIN table_4 e > > LEFT OUTER JOIN ( > > SELECT fld_1,fld_2,fld_3,fld_4,row_id,tran_date, > > ROW_NUMBER() OVER (PARTITION BY fld_1,fld_2,fld_3,fld_4 ORDER BY > > tran_date DESC) row_num > > FROM my_table > > WHERE deleted_id = 0 > > ) a ON 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.row_num = 1; > > > > Should do it. You could also perhaps do something with DISTINCT ON > > instead of using ROW_NUMBER(). That might be a bit more efficient, but > > it's unlikely to matter too much since there are only 167 rows in that > > table. > > I would expect a lateral query to be better here. But that would put it back to executing the subquery 11088 times. I wrote it in a way to avoid that. David
On 2020-05-26 1:45 PM, David Rowley wrote: > On Tue, 26 May 2020 at 22:31, Frank Millman <frank@chagford.com> wrote: > >> Thank you David. I tried that and it produced the correct result in >> 53ms, which is what I am looking for. >> >> It will take me some time to understand it fully, so I have some >> homework to do! > > The main problem with your previous query was that the subquery was > being executed 11088 times and could only ever find anything 167 > times. The remaining number of times nothing would be found. > > I just changed the subquery which would be executed once per output > row and altered it so it became a subquery that's joined and only > executed once. The ROW_NUMBER() is a windowing function, which is > explained in [1]. I used this to get the row_id of the record with > the lowest tran_date, just like you were doing with the ORDER BY > tran_date DESC LIMIT 1, but the subquery with the windowing function > gets them all at once, rather than doing it in a way that requires it > to be executed once for each row in the top-level query. In this case, > the functionality that the LIMIT 1 does in your query is achieved with > "AND a.row_num = 1;" in my version. This is pretty fast to execute > once due to there only being 167 rows. > > It's also important to know that there may be cases where the method I > proposed is slower. For example, if my_table was very large and > contained rows that were not in table_1 to table_4. Since the subquery > in my version calculates everything then it could be wasteful to do > that for values that would never be used. For you, you have foreign > keys that ensure my_table does not contain records that are not in the > other tables, but you could still see this issue if you were to add > some restrictive WHERE clause to the outer query. Perhaps this won't > be a problem for you, but it's likely good to know. > > [1] https://www.postgresql.org/docs/current/tutorial-window.html > Thanks very much for the explanation. I will go through it carefully. For the record, your query works without modification in both Sql Server and sqlite3. It is also much faster in all three cases - all around 0.005 sec instead of 0.05 sec. Frank
On 2020-05-26 12:04 PM, David Rowley wrote:
>
> Since "my_table" is small, you'd likely be much better doing a manual
> rewrite of the query to join a subquery containing the required
> details from "my_table". It looks like you want the row_id from the
> latest tran_date for each fld_N column. So something like:
>
> SELECT a.row_id
> FROM table_1 b
> CROSS JOIN table_2 c
> CROSS JOIN table_3 d
> CROSS JOIN table_4 e
> LEFT OUTER JOIN (
> SELECT fld_1,fld_2,fld_3,fld_4,row_id,tran_date,
> ROW_NUMBER() OVER (PARTITION BY fld_1,fld_2,fld_3,fld_4 ORDER BY
> tran_date DESC) row_num
> FROM my_table
> WHERE deleted_id = 0
> ) a ON 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.row_num = 1;
>
> Should do it. You could also perhaps do something with DISTINCT ON
> instead of using ROW_NUMBER(). That might be a bit more efficient, but
> it's unlikely to matter too much since there are only 167 rows in that
> table.
>
I have studied the above SELECT, and I now more or less understand it. I
would not have come up with that unaided, so many thanks.
I tried DISTINCT ON, and it was very efficient, but unfortunately that
is not supported by SQL Server or sqlite3.
Then I came up with this alternative, which works on all three platforms
and seems a bit faster than the above -
SELECT a.row_id FROM (
SELECT row_id,
ROW_NUMBER() OVER (PARTITION BY fld_1, fld_2, fld_3, fld_4
ORDER BY tran_date DESC) row_num
FROM my_table
WHERE deleted_id = 0
) as a
WHERE a.row_num = 1
Do you see any problem with this?
Thanks
Frank