0

I am analyzing the query which takes several minutes to execute and I want it to be a bit faster:

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM filings AS filing
WHERE (filing.sources_id != 10 OR filing.action_date < NOW()::date)
AND (filing_type_id IN (4538,5080))
ORDER BY filing.action_date desc LIMIT 10

Response:

                                                                                  QUERY PLAN                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..4124.25 rows=10 width=704) (actual time=128412.522..158528.958 rows=10 loops=1)
   Buffers: shared hit=177017 read=162030 dirtied=81 written=34435
   ->  Index Scan Backward using filings_action_date_idx on filings filing  (cost=0.56..6437894.65 rows=15612 width=704) (actual time=128412.429..158528.832 rows=10 loops=1)
         Filter: ((filing_type_id = ANY ('{4538,5080}'::integer[])) AND ((sources_id <> 10) OR (action_date < (now())::date)))
         Rows Removed by Filter: 351983
         Buffers: shared hit=177017 read=162030 dirtied=81 written=34435
 Planning time: 5.454 ms
 Execution time: 158529.062 ms
(8 rows)

I don't understand why my index is useless and filtering of 300K rows is used instead

Filter: ((filing_type_id = ANY ('{4538,5080}'::integer[])) AND ((sources_id <> 10) OR (action_date < (now())::date)))

although index is in place:

"filings_filing_type_id_sources_id_action_date_idx" btree (filing_type_id, sources_id, action_date)

Instead this index is in action:

"filings_action_date_idx" btree (action_date)

according to the query plan

Update

postgres version:

SELECT version();


PostgreSQL 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit (1 row)

number of rows in the table:

select count(*) from filings;
  count   
----------
 37723118
(1 row)

And

select relname, last_analyze, last_autoanalyze from pg_stat_all_tables where relname='filings';

gives:

 relname | last_analyze |       last_autoanalyze       
---------+--------------+------------------------------
 filings |              | 2020-03-26 18:32:09.88901+00
(1 row)
  • What is the PostgreSQL version ? What is the total number of rows in the table filings ? What is the total number of runs for the query without the LIMIT 10 clause ? Can you post output of select relname, last_analyze, last_autoanalyze from pg_stat_all_tables where relname='filings' (in the right schema) ? – pifor Mar 27 at 9:37
  • @pifor What is the total number of runs for the query without the LIMIT 10 clause ? sorry, how to check it? The rest is added in the update above – Maxim Yefremov Mar 27 at 9:56
  • @MaximYefremov, I just mean number of rows: just run SELECT count(*) FROM filings AS filing WHERE (filing.sources_id != 10 OR filing.action_date < NOW()::date) AND (filing_type_id IN (4538,5080)). – pifor Mar 27 at 10:02
  • @LaurenzAlbe I can't believe it, I changed ORDER BY filing.action_date to ORDER BY filing.action_date + interval '1 second' and now it takes less than 1 second, this is a success)) – Maxim Yefremov Mar 27 at 10:08