Re: Slow SELECT

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 som

Re: Slow SELECT

On 2020-05-26 1:45 PM, David Rowley wrote: On Tue, 26 May 2020 at 22:31, Frank Millman 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 mai

Re: Slow SELECT

On Tue, 26 May 2020 at 23:41, Vik Fearing 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_i

Re: Slow SELECT

On Tue, 26 May 2020 at 22:31, Frank Millman 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 th

Re: Slow SELECT

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 some

Re: Slow SELECT

On 2020-05-26 12:04 PM, David Rowley wrote: On Tue, 26 May 2020 at 19:23, Frank Millman 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 po

Re: Slow SELECT

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 2. Copy/Pa

Re: Slow SELECT

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 > wrote

Re: Slow SELECT

On Tue, 26 May 2020 at 19:23, Frank Millman 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. Mos

Re: Slow SELECT

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 2. Copy/Paste the output into the fantastic https://explain

Re: Slow SELECT

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 > wrote:     Hi all     I have a SELECT that runs over 5 t

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 > wrote: Hi all I have a SELECT that runs over 5 time

Re: Slow SELECT

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 > wrote: Hi all I have a SELECT that runs over 5 times slower on PostgreSQL compared with Sql Serv

Re: Slow SELECT

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

Re: Slow SELECT

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

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 REFEREN