0

I have a materialized view with about 1.6 million entries that gets refreshed every hour or so. I have a && and ANY() as the filters in the query. I observed the query was taking too long (over 2 minutes) to execute.

I then tried the basic SELECT * FROM components_view and it was taking about 25 seconds to execute.

explain analyze select * from components_view;
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on components_view  (cost=0.00..350816.28 rows=1652628 width=886) (actual time=1.007..23978.237 rows=1652628 loops=1)
 Planning time: 0.103 ms
 Execution time: 25605.588 ms
(3 rows)


I then tried with one of the filters and the execution time increased by about 10 seconds

explain analyze select * from components_view where (  false = any(ignored_array) );
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on components_view  (cost=0.00..371474.13 rows=1652628 width=886) (actual time=5.670..32654.284 rows=1652628 loops=1)
   Filter: (false = ANY (ignored_array))
 Planning time: 12.219 ms
 Execution time: 34106.971 ms
(4 rows)

Because of the slowness, the very purpose of choosing the materialized view is not being met.

There are no indexes on the columns that I"m filtering on (ignored_array and ids_array).

Any help here is appreciated. Thanks!

Edit: Here's the explain (analyze, buffers, timing) for the queries

explain (analyze, buffers, timing) select * from components_view;
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on components_view  (cost=0.00..350856.79 rows=1656579 width=888) (actual time=1.073..32332.136 rows=1652628 loops=1)
   Buffers: shared hit=57142 read=277149
 Planning time: 12.837 ms
 Execution time: 34038.656 ms
(4 rows)

explain (analyze, buffers, timing) select * from components_view where (  false = any(ignored_array) );;
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on components_view  (cost=0.00..371564.03 rows=1656579 width=888) (actual time=3.516..32466.766 rows=1652628 loops=1)
   Filter: (false = ANY (ignored_array))
   Buffers: shared hit=57174 read=277117
 Planning time: 0.123 ms
 Execution time: 34089.644 ms
(5 rows)
6
  • 1
    It didn't pick Parallel Seq Scan because max_parallel_workers_per_gather is 0 by default in 9.6. Increasing it may or may not help, depending on whether you're currently I/O bound. – AdamKG Nov 18 '20 at 14:56
  • 3
    So the query had to read about 2GB of data (277149 blocks * 8kb) from disk and that took 34 seconds. That boils down to roughly 60MB/Second - sounds as if you have a slow harddisk – a_horse_with_no_name Nov 18 '20 at 15:42
  • So this seems to be a caching issue. Execution time depends on how much of the table is cached, the additional condition does not make a difference. – Laurenz Albe Nov 18 '20 at 17:13
  • @a_horse_with_no_name Thank you for the analysis. I'll try the same dump on a system with SSD and see if there's any performance improvement. – Pavan Nov 18 '20 at 17:57
  • 1
    You can use pg_prewarm to cache it, but that won't be a permanent effect. But faster storage might be a good idea. – Laurenz Albe Nov 18 '20 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.