4

I'm experiencing a strange variation of filter performance depending on the value in WHERE condition with this query:

SELECT MIN(revenue) AS min_revenue, 
       MAX(revenue) AS max_revenue 
FROM   v_premises_filter 
WHERE  area IS NOT NULL 
AND    v_premises_filter.scat_group_code =1

Query with =1 condition executes much faster than =23.

I have noticed that the drastic slowdown happens when aggregated column (revenue in the example below) has only NULL values for rows matching the WHERE condition.

I would understand if this was happening when using index lookup, but it happens when using filter - which to my knowledge should scan all rows.

With scat_group_code =1, the query executes quickly:

Result  (cost=2.92..2.93 rows=1 width=0) (actual time=0.239..0.241 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.43..1.46 rows=1 width=8) (actual time=0.134..0.135 rows=1 loops=1)
          ->  Index Scan using v_premises_filter_revenue_idx on v_premises_filter  (cost=0.43..310356.33 rows=301212 width=8) (actual time=0.130..0.130 rows=1 loops=1)
                Index Cond: (revenue IS NOT NULL)
                Filter: ((area IS NOT NULL) AND (scat_group_code = 1))
                Rows Removed by Filter: 20
  InitPlan 2 (returns $1)
    ->  Limit  (cost=0.43..1.46 rows=1 width=8) (actual time=0.094..0.096 rows=1 loops=1)
          ->  Index Scan Backward using v_premises_filter_revenue_idx on v_premises_filter v_premises_filter_1  (cost=0.43..310356.33 rows=301212 width=8) (actual time=0.091..0.091 rows=1 loops=1)
                Index Cond: (revenue IS NOT NULL)
                Filter: ((area IS NOT NULL) AND (scat_group_code = 1))
                Rows Removed by Filter: 17
Planning time: 0.359 ms
Execution time: 0.276 ms

However, with scat_group_code =23, the same query with the same execution plan takes much more time:

Result  (cost=129.15..129.16 rows=1 width=0) (actual time=8138.644..8138.669 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.43..64.58 rows=1 width=8) (actual time=4094.036..4094.036 rows=0 loops=1)
          ->  Index Scan using v_premises_filter_revenue_idx on v_premises_filter  (cost=0.43..310356.33 rows=4838 width=8) (actual time=4094.032..4094.032 rows=0 loops=1)
                Index Cond: (revenue IS NOT NULL)
                Filter: ((area IS NOT NULL) AND (scat_group_code = 23))
                Rows Removed by Filter: 1631527
  InitPlan 2 (returns $1)
    ->  Limit  (cost=0.43..64.58 rows=1 width=8) (actual time=4044.569..4044.569 rows=0 loops=1)
          ->  Index Scan Backward using v_premises_filter_revenue_idx on v_premises_filter v_premises_filter_1  (cost=0.43..310356.33 rows=4838 width=8) (actual time=4043.757..4043.757 rows=0 loops=1)
                Index Cond: (revenue IS NOT NULL)
                Filter: ((area IS NOT NULL) AND (scat_group_code = 23))
                Rows Removed by Filter: 1631527
Planning time: 0.260 ms
Execution time: 8138.770 ms

What would be the best way to optimise this query (aside from getting more memory to persuade query planner to use indexes instead of filters)?

Additional information

Here is the view structure from \d+ command:

                      Materialized view "public.v_premises_filter"
     Column      |       Type       | Modifiers | Storage  | Stats target | Description 
-----------------+------------------+-----------+----------+--------------+-------------
 uarn            | bigint           |           | plain    |              | 
 lat             | double precision |           | plain    |              | 
 long            | double precision |           | plain    |              | 
 la_code         | text             |           | extended |              | 
 la_desc         | text             |           | extended |              | 
 msoa_code       | text             |           | extended |              | 
 msoa_desc       | text             |           | extended |              | 
 lsoa_code       | text             |           | extended |              | 
 lsoa_desc       | text             |           | extended |              | 
 postcode        | text             |           | extended |              | 
 street          | text             |           | extended |              | 
 empty           | boolean          |           | plain    |              | 
 rateable_value  | bigint           |           | plain    |              | 
 area            | numeric          |           | main     |              | 
 employees       | numeric          |           | main     |              | 
 employee_cost   | numeric          |           | main     |              | 
 break_even      | numeric          |           | main     |              | 
 margin          | double precision |           | plain    |              | 
 revenue         | double precision |           | plain    |              | 
 ba_ref          | text             |           | extended |              | 
 scat_code       | smallint         |           | plain    |              | 
 scat_group_code | smallint         |           | plain    |              | 
 sic_code        | character(1)[]   |           | extended |              | 
Indexes:
    "v_premises_filter_area_idx" btree (area)
    "v_premises_filter_ba_ref_idx" btree (ba_ref)
    "v_premises_filter_break_even_idx" btree (break_even)
    "v_premises_filter_employee_cost_idx" btree (employee_cost)
    "v_premises_filter_employees_idx" btree (employees)
    "v_premises_filter_empty_idx" btree (empty)
    "v_premises_filter_la_code_la_desc_idx" btree (la_code, la_desc)
    "v_premises_filter_lat_long_idx" btree (lat, long)
    "v_premises_filter_lsoa_code_lsoa_desc_idx" btree (lsoa_code, lsoa_desc)
    "v_premises_filter_margin_idx" btree (margin)
    "v_premises_filter_msoa_code_msoa_desc_idx" btree (msoa_code, msoa_desc)
    "v_premises_filter_postcode_idx" btree (postcode text_pattern_ops)
    "v_premises_filter_postcode_street_idx" btree (postcode, street)
    "v_premises_filter_rateable_value_idx" btree (rateable_value)
    "v_premises_filter_revenue_idx" btree (revenue)
    "v_premises_filter_scat_code_idx" btree (scat_code)
    "v_premises_filter_scat_group_code_idx" btree (scat_group_code)
    "v_premises_filter_scat_group_code_idx1" btree (scat_group_code) WHERE area IS NOT NULL
    "v_premises_filter_sic_code_idx" gin (sic_code)
    "v_premises_filter_uarn_idx" btree (uarn)

Regarding cardinality, I'm confident that there is nothing obvious happening like fan-trap in the view creation because we do consistency checks on a few column totals and the row count is matching number of rows in master source table of the JOIN.

The count of filtered rows differs so much because of genuine difference in amount of NULL values in the groups:

=> SELECT scat_group_code, COUNT(*), COUNT(area), COUNT(revenue)
-> FROM v_premises_filter
-> WHERE scat_group_code IN (1,23)
-> GROUP BY scat_group_code;
 scat_group_code | count  | count  | count  
-----------------+--------+--------+--------
              23 |   6513 |   6513 |      0
               1 | 359813 | 359813 | 359813
(2 rows)

=> SELECT COUNT(*), COUNT(revenue) FROM v_premises_filter;
  count  |  count  
---------+---------
 1909175 | 1631527
(1 row)

I also noticed that a makeshift MAX() function works much better than the MAX function itself:

=> EXPLAIN ANALYZE SELECT revenue FROM v_premises_filter
WHERE area IS NOT NULL AND scat_group_code = 23
ORDER BY revenue DESC LIMIT 1;

 Limit  (cost=0.43..55.28 rows=1 width=8) (actual time=0.222..0.223 rows=1 loops=1)
   ->  Index Scan Backward using v_premises_filter_revenue_idx on v_premises_filter  (cost=0.43..314199.84 rows=5728 width=8) (actual time=0.218..0.218 rows=1 loops=1)
         Filter: ((area IS NOT NULL) AND (scat_group_code = 23))
         Rows Removed by Filter: 117
 Planning time: 0.161 ms
 Execution time: 0.245 ms
(6 rows)

However, it appears to run slower for scat_group_code values like 1, that match bigger count of rows, so not an ideal solution in my use case. Besides, at this point I'm very interested to find out why MAX()/MIN() do not run fast enough.

I use PostgreSQL 9.5.6.

Improve this question
0

2 Answers 2

Reset to default
4

You have a lot of indexes and my first impuls would be to check whether all of them are actually used and remove the unused ones.

The optimal index for your query is not there, yet, though: a multicolumn index on (scat_group_code, revenue). Since this is a materialized view, it's reasonable to assume a read-only situation, which is perfect for index-only scans.

Also, since the table (many columns, some potentially big text columns) is much wider than the index (two columns, fewer rows for the partial variant), an index-only scan is particularly efficient here. Size matters.

If this particular query is very common / important, I would go one step further and make it a partial, multicolumn index:

CREATE INDEX ON v_premises_filter (scat_group_code, revenue)
WHERE area IS NOT NULL
AND   revenue IS NOT NULL;

I added the 2nd condition revenue IS NOT NULL because min() and max() ignore NULL values. I tested with Postgres 9.6. You may have to add the logically redundant predicate AND revenue IS NOT NULL to your query in older versions to make the query planner understand the index is applicable.

The index your query used in your tests (v_premises_filter_revenue_idx) is on (revenue), which is pretty inefficient for a predicate on scat_group_code. Adding that column as first index item makes a big difference, because Postgres can cheaply grab only the share with matching scat_group_code now. Where your 2nd query had to filter 1631527 rows, (Rows Removed by Filter: 1631527") it only has to filter much fewer rows with area is null OR revenue is null for the simple index and none at all for the partial index.

Also, since irrelevant rows are excluded from the index, it is substantially smaller, which contribute3s to performance as well.

You should see two index-only scans and a much faster query in either case now.

Related:

Why the fluctuation?

Your table is big (1909175 rows) and data distribution is obviously irregular. The default statistics_target setting of 100 is probably too small to gather enough details. Postgres decides to use a bad query plan for your 2nd query, based on insufficient information. It might help to increase the STATISTICS setting for scat_group_code, revenue and area.
But you won't need this with the suggested new index any more, because an index-only scan on the new index is always the best plan.

Consider this related answer from just yesterday:

Improve this answer
3
  • Thank you Erwin for such a detailed response. I use PostgreSQL 9.5.6. I tried adding extra NOT NULL condition, but it still follows same execution plan taking several seconds. Adding the INDEX like the one you suggested works great. However these queries are used to calculate remaining parameter boundaries for OLAP-style filtering, so I would need about 40 indexes for all possible queries (non-null revenue rows for given scat_group_code, non-null revenue rows for given sic_code, etc). Is this the best way to go in my scenario?
    – Paul T.
    Commented Mar 21, 2017 at 21:32
  • I thought about increasing STATISTICS setting, however for scat_group_code field all its possible non-NULL values are listed explicitly in pg_stats.most_common_vals column, there are no histogram_bounds for it.
    – Paul T.
    Commented Mar 21, 2017 at 21:42
  • @PaulT.: 40 indexes may seem a lot, but you already have 21 (and some of those are probably just ballast). Also, I see only 23 columns, so why 40? Either way, I have had use cases with several hundred partial indexes. For a read-only table, there is no write performance penalty ...
    – Erwin Brandstetter
    Commented Mar 22, 2017 at 0:55
1

Look at both of them.. Your index conditions are the same on both..

Index Cond: (revenue IS NOT NULL)"
Filter: ((area IS NOT NULL) AND (scat_group_code = 1))"
Rows Removed by Filter: 20"
Index Cond: (revenue IS NOT NULL)"
Filter: ((area IS NOT NULL) AND (scat_group_code = 1))"
Rows Removed by Filter: 17"

And the the second one

Index Cond: (revenue IS NOT NULL)"
Filter: ((area IS NOT NULL) AND (scat_group_code = 23))"
Rows Removed by Filter: 1631527"
Index Cond: (revenue IS NOT NULL)"
Filter: ((area IS NOT NULL) AND (scat_group_code = 23))"
Rows Removed by Filter: 1631527"

In both of these queries you're returning one row, but in the top you're returning 1/(1+20+17). In the bottom you're returning 1/(1+1631527+1631527)

Without any idea of what you're removing from, v_premises_filter, we can say that these two queries are presenting massively different row counts.

You seem to have a fan-trap or something in the view.

Improve this answer
1
  • Evan, I have updated the question with some cardinality details. Hope it is more clear now, what data are in the table.
    – Paul T.
    Commented Mar 21, 2017 at 10:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.