Postgresql – Btee-index select query takes long time

postgresqlpostgresql-performance

My query is below:

WITH CTE AS(
         SELECT Id,
                SaleTime
         FROM   tbl_sales_log
         WHERE  saletime > ’2019 -06 -01 00:00:00’
                AND saletime < ’2019 -06 -30 23:59:59’
                AND centerid IN (708, 805, 432, 403)
                AND itemid IN (3, 6)
         ORDER BY
                saletime DESC limit 1000 offset 0
     ) 

SELECT Id,
       col1,
       col2,
       col3,
       col4,
       col5,
       col6,
       col7,
       col8,
       saletime
FROM   my_table_6 R
WHERE  R.id = ANY(
           SELECT Id
           FROM   CTE limit 1000
       )
       AND R.saletime > ’2019 -06 -01 00:00:00’
       AND R.saletime < ’2019 -06 -30 23:59:59’
ORDER BY
       R.saletime DESC limit 1000;

Explain (analyze,buffers) is below:

my_table_6 has 35 million records. It has a btree-index for the column Id.

When I select just 1000 records it takes almost 30 seconds.

How can I optimize it?

Note: PostgreSQL 12 server is running on Windows.

CTE query executed within 500 millisecond, but querying from my_table_6 takes more time. I have checked this many times.

Best Answer

Please don't post execution plans as screen shots.

The problem is that the index scan on my_table_6 takes 26.5 milliseconds on average and is executed 1000 times.

Try VACUUM on the table to remove dead rows. If the index is bloated, REINDEX might help.

Related Solutions

Postgresql – pg_restore: [custom archiver] could not read from input file: end of file

I would suggest you try to do something along the line of:

SELECT count(*) FROM mytable WHERE my_primary_key < value ;

... and find at which point it fails. [This may be tedious, but you can just cut in half repeatedly, until you find the value that first fails.]

You will most probably need to alter the different settings that affect index usage, because you actually want to force the database to use the index, even if it actually must scan 99% of the table. You want it not to scan the page that booms.

If you can get SELECT to give you most of the data, you can then do something such as:

CREATE TABLE my_table_2 AS 
SELECT * FROM my_table WHERE my_primary_key < value;

and later on:

ALTER TABLE my_table RENAME TO my_table_old ;
ALTER TABLE my_table_2 RENAME TO my_table ;

... and you'll have all the data that could be retrieved. I wouldn't drop the old table, in case someone finds later on a better method of retrieving the missing info.

Best of luck.

How to Index Two Tables for Optimized JOIN Queries in PostgreSQL

For starters, get your LEFT JOIN right and try to get rid of total rows (as discussed in comments):

Query

Updated to your comment:

I only want to returns rows from request_logs that have a parallel entry in response_logs with non nil status:

SELECT q.uuid
     , s.status
     , q.method
     , q.requesturi
     , q.accessed
     , q.payload reqpayload
     , s.payload respayload
--   , COUNT(*) OVER() AS total_rows  -- see below
FROM   request_logs q
JOIN   response_logs s USING (uuid)   -- not LEFT JOIN
WHERE  q.accountid = 2
AND    q.requesturi NOT ILIKE '/v1/sessions%'
AND    q.accessed BETWEEN '2018-01-01 15:04:05 +0' AND '2019-01-02 15:04:05+0'
AND    q.applicationid = 1
AND    s.status IS NOT NULL           -- see below
ORDER  BY q.accessed DESC
LIMIT  1001;                          -- see below

Your LEFT [OUTER] JOIN burns down to a plain [INNER] JOIN. Related:

Query with LEFT JOIN not returning rows for count of 0

COUNT(*) OVER() AS total_rows is expensive for counts substantially bigger than LIMIT (and you are expecting "10m+ rows"). Maybe it's good enough to use LIMIT 1001, only use the first 1000 rows, check the row count and if it's 1001 then you know there are "more than 1000 matching rows". Related:

Best way to get result count before LIMIT was applied

Indexes

If (like you commented)

the only 'moving parts' in this query are the dates (from and to) that are compared with accessed

... and more than a few rows are excluded with the other predicates on applicationid, requesturi and accountid, then a partial index should help read performance (a lot):

CREATE INDEX ON request_logs (uuid, accessed DESC)
WHERE  accountid = 2
AND    requesturi NOT ILIKE '/v1/sessions%'
AND    applicationid = 1

And if a substantial percentage of rows is eliminated by status IS NOT NULL, also:

CREATE INDEX ON response_logs (uuid)
WHERE  status IS NOT NULL;

It may pay to append the columns status and payload as index columns if you can get index-only scans out of it. Some preconditions apply.