1

In 9.6.3 adding NULLIF() in the WHERE clause changes the plan from a bitmap heap scan to an index scan and changes execution time from about 0.3ms to 43,000ms.

Here is a related question: https://stackoverflow.com/questions/21062148/how-to-query-postgres-on-optional-params

This is the fast query and plan:

SELECT
        c.name_first, c.name_last, c.postal_code,
        v.tag as plate_number, v.tag_state as plate_state, v.year, v.make, v.model
FROM customers_vehicles AS v
        JOIN customers AS c ON (v.customer_id = c.id)
WHERE ('XXXXXX'::text is null OR v.tag='XXXXXX');
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                  QUERY PLAN                                                                   │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Nested Loop  (cost=824.67..451863.32 rows=43830 width=228) (actual time=0.202..0.203 rows=1 loops=1)                                          │
│   ->  Bitmap Heap Scan on customers_vehicles v  (cost=824.12..121946.77 rows=43830 width=164) (actual time=0.104..0.104 rows=1 loops=1)       │
│         Recheck Cond: (tag = 'XXXXXX'::text)                                                                                                  │
│         Heap Blocks: exact=1                                                                                                                  │
│         ->  Bitmap Index Scan on customers_vehicles_tag_idx  (cost=0.00..813.16 rows=43830 width=0) (actual time=0.090..0.090 rows=1 loops=1) │
│               Index Cond: (tag = 'XXXXXX'::text)                                                                                              │
│   ->  Index Scan using customers_pkey on customers c  (cost=0.56..7.52 rows=1 width=128) (actual time=0.093..0.094 rows=1 loops=1)            │
│         Index Cond: (id = v.customer_id)                                                                                                      │
│ Planning time: 0.268 ms                                                                                                                       │
│ Execution time: 0.256 ms                                                                                                                      │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

And this is the slow query and plan:

SELECT
        c.name_first, c.name_last, c.postal_code,
        v.tag as plate_number, v.tag_state AS plate_state, v.year, v.make, v.model
FROM customers_vehicles AS v
        JOIN customers as c on (v.customer_id = c.id)
WHERE (nullif('XXXXXX'::text,'') IS NULL OR v.tag='XXXXXX');
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                                    QUERY PLAN                                                                                    │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Nested Loop  (cost=1.12..7509681.67 rows=87441 width=228) (actual time=5462.111..42209.513 rows=1 loops=1)                                                                       │
│   ->  Index Scan using customers_vehicles_customer_id_idx on customers_vehicles v  (cost=0.56..6921180.68 rows=87441 width=164) (actual time=5462.023..42209.423 rows=1 loops=1) │
│         Filter: ((NULLIF('XXXXXX'::text, ''::text) IS NULL) OR (tag = 'XXXXXX'::text))                                                                                             │
│         Rows Removed by Filter: 8766005                                                                                                                                          │
│   ->  Index Scan using customers_pkey on customers c  (cost=0.56..6.72 rows=1 width=128) (actual time=0.080..0.081 rows=1 loops=1)                                               │
│         Index Cond: (id = v.customer_id)                                                                                                                                         │
│ Planning time: 0.209 ms                                                                                                                                                          │
│ Execution time: 42209.549 ms                                                                                                                                                     │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

The only difference is in the slow query in the WHERE clause: 'XXXXXX' IS NULL changes to nullif('XXXXXX','') IS NULL. It seems the planner cannot optimize out the first condition if it contains NULLIF(). Is there a way to coerce it into doing so?

0

Try changing your predicate to the following:

WHERE ('XXXXXX'::text is null OR v.tag='XXXXXX' OR 'XXXXXX' = '');

As this is effectively what the NULLIF statement is doing from a logic standpoint. I'm assuming 'XXXXXX' is a column in this scenario and not just a random string of X's, correct?

  • 'XXXXXX' would be an input parameter, it could be replaced with $1. – Ian Timothy Jul 10 '17 at 18:06
  • @IanTimothy This should do it for you even if an empty string is passed in as a parameter. – John Eisbrener Jul 10 '17 at 18:07
  • Man so easy. Just need another brain on it sometimes. – Ian Timothy Jul 10 '17 at 18:26

Your Answer

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

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