Why is an OR statement slower than UNION?

Question

Database version: PostgreSQL 12.6

I have a table with 600,000 records.

The table has the columns:

• name (varchar)
• location_type (int) enum values: (1,2,3)
• ancestry (varchar)

Indexes:

• ancestry (btree)

The ancestry column is a way to build a tree where every row has an ancestry containing all parent ids separated by '/'.

Consider the following example:

id	name	ancestry
1	root	null
5	node	'1'
12	node	'1/5'
22	leaf	'1/5/12'

The following query takes 686 ms to execute:

SELECT * FROM geolocations
WHERE EXISTS (
   SELECT 1 FROM geolocations g2
   WHERE g2.ancestry =
      CONCAT(geolocations.ancestry, '/', geolocations.id)
)

This query runs in 808 ms:

SELECT * FROM geolocations
WHERE location_type = 2

When combining both queried with an OR, it takes around 4475 ms to finish if it ever finishes.

SELECT * FROM geolocations
WHERE EXISTS (
   SELECT 1 FROM geolocations g2
   WHERE g2.ancestry =
      CONCAT(geolocations.ancestry, '/', geolocations.id)
) OR location_type = 2

Explain:

[
  {
    "Plan": {
      "Node Type": "Seq Scan",
      "Parallel Aware": false,
      "Relation Name": "geolocations",
      "Alias": "geolocations",
      "Startup Cost": 0,
      "Total Cost": 2760473.54,
      "Plan Rows": 582910,
      "Plan Width": 68,
      "Filter": "((SubPlan 1) OR (location_type = 2))",
      "Plans": [
        {
          "Node Type": "Index Only Scan",
          "Parent Relationship": "SubPlan",
          "Subplan Name": "SubPlan 1",
          "Parallel Aware": false,
          "Scan Direction": "Forward",
          "Index Name": "index_geolocations_on_ancestry",
          "Relation Name": "geolocations",
          "Alias": "g2",
          "Startup Cost": 0.43,
          "Total Cost": 124.91,
          "Plan Rows": 30,
          "Plan Width": 0,
          "Index Cond": "(ancestry = concat(geolocations.ancestry, '/', geolocations.id))"
        }
      ]
    },
    "JIT": {
      "Worker Number": -1,
      "Functions": 8,
      "Options": {
        "Inlining": true,
        "Optimization": true,
        "Expressions": true,
        "Deforming": true
      }
    }
  }
]

While combining them with a union takes 1916 ms:

SELECT * FROM geolocations
WHERE EXISTS (
   SELECT 1 FROM geolocations g2
   WHERE g2.ancestry =
      CONCAT(geolocations.ancestry, '/', geolocations.id)
) UNION SELECT * FROM geolocations WHERE location_type = 2

Explain

[
  {
    "Plan": {
      "Node Type": "Unique",
      "Parallel Aware": false,
      "Startup Cost": 308693.44,
      "Total Cost": 332506.74,
      "Plan Rows": 865938,
      "Plan Width": 188,
      "Plans": [
        {
          "Node Type": "Sort",
          "Parent Relationship": "Outer",
          "Parallel Aware": false,
          "Startup Cost": 308693.44,
          "Total Cost": 310858.29,
          "Plan Rows": 865938,
          "Plan Width": 188,
          "Sort Key": [
            "geolocations.id",
            "geolocations.name",
            "geolocations.location_type",
            "geolocations.pricing",
            "geolocations.ancestry",
            "geolocations.geolocationable_id",
            "geolocations.geolocationable_type",
            "geolocations.created_at",
            "geolocations.updated_at",
            "geolocations.info"
          ],
          "Plans": [
            {
              "Node Type": "Append",
              "Parent Relationship": "Outer",
              "Parallel Aware": false,
              "Startup Cost": 15851.41,
              "Total Cost": 63464.05,
              "Plan Rows": 865938,
              "Plan Width": 188,
              "Subplans Removed": 0,
              "Plans": [
                {
                  "Node Type": "Hash Join",
                  "Parent Relationship": "Member",
                  "Parallel Aware": false,
                  "Join Type": "Inner",
                  "Startup Cost": 15851.41,
                  "Total Cost": 35074.94,
                  "Plan Rows": 299882,
                  "Plan Width": 68,
                  "Inner Unique": true,
                  "Hash Cond": "(concat(geolocations.ancestry, '/', geolocations.id) = (g2.ancestry)::text)",
                  "Plans": [
                    {
                      "Node Type": "Seq Scan",
                      "Parent Relationship": "Outer",
                      "Parallel Aware": false,
                      "Relation Name": "geolocations",
                      "Alias": "geolocations",
                      "Startup Cost": 0,
                      "Total Cost": 13900.63,
                      "Plan Rows": 599763,
                      "Plan Width": 68
                    },
                    {
                      "Node Type": "Hash",
                      "Parent Relationship": "Inner",
                      "Parallel Aware": false,
                      "Startup Cost": 15600.65,
                      "Total Cost": 15600.65,
                      "Plan Rows": 20061,
                      "Plan Width": 12,
                      "Plans": [
                        {
                          "Node Type": "Aggregate",
                          "Strategy": "Hashed",
                          "Partial Mode": "Simple",
                          "Parent Relationship": "Outer",
                          "Parallel Aware": false,
                          "Startup Cost": 15400.04,
                          "Total Cost": 15600.65,
                          "Plan Rows": 20061,
                          "Plan Width": 12,
                          "Group Key": [
                            "(g2.ancestry)::text"
                          ],
                          "Plans": [
                            {
                              "Node Type": "Seq Scan",
                              "Parent Relationship": "Outer",
                              "Parallel Aware": false,
                              "Relation Name": "geolocations",
                              "Alias": "g2",
                              "Startup Cost": 0,
                              "Total Cost": 13900.63,
                              "Plan Rows": 599763,
                              "Plan Width": 12
                            }
                          ]
                        }
                      ]
                    }
                  ]
                },
                {
                  "Node Type": "Seq Scan",
                  "Parent Relationship": "Member",
                  "Parallel Aware": false,
                  "Relation Name": "geolocations",
                  "Alias": "geolocations_1",
                  "Startup Cost": 0,
                  "Total Cost": 15400.04,
                  "Plan Rows": 566056,
                  "Plan Width": 68,
                  "Filter": "(location_type = 2)"
                }
              ]
            }
          ]
        }
      ]
    },
    "JIT": {
      "Worker Number": -1,
      "Functions": 15,
      "Options": {
        "Inlining": false,
        "Optimization": false,
        "Expressions": true,
        "Deforming": true
      }
    }
  }
]

Why does PostgreSQL execute the OR query much slower?

Answer 1

Jeff already hinted at this, but I feel the need to point out the elephant in the room:
The two queries are not equivalent!

UNION removes all duplicates across the SELECT list.
While the other query with OR keeps them.

You have SELECT * FROM geolocations, and no other tables in the FROM list. So if there are no duplicate rows in the table (which is guaranteed by any UNIQUE index on NOT NULL columns including PRIMARY KEY and UNIQUE constraints), there cannot be duplicates in the result and the two queries are equivalent after all. But any JOIN or any SELECT list with a (not-unique) subset of columns can introduce duplicate rows in the result!

Using UNION ALL instead is even further off. It produces duplicates that OR-ed predicates will not. If the same row qualifies for multiple OR-ed predicates, it qualifies once. Rewriting with UNION ALL will select that same row multiple times.

There is no way to filter "bad" duplicates and keep the "good" ones with UNION / UNION ALL. So Postgres cannot generally replace cases of "ugly OR" with UNION plans. Even where it could, it's not certain that UNION will, in fact, be faster.

But Postgres can typically combine multiple OR-ed predicates on the same table in a bitmap index scan. See:

• What is a “bitmap index”?

An "ugly OR" is where predicates on different underlying tables are OR-ed together. (Or even the same table, but separate instances like in your case.) This can make queries more expensive, even when no indexes are involved. But it gets particularly expensive when an efficient index scan is foiled by this. (Indexes on different tables cannot be combined in a bitmap index scan!) It typically matters most for selective queries returning a small percentage of rows from underlying big tables. When more than a few percent of all rows have to be read anyway, index scans lose their power. Ugly ORs don't hurt as much in those queries.

Related blog post by Laurenz Albe:

• https://www.cybertec-postgresql.com/en/avoid-or-for-better-performance/

Solutions

First of all, your use of CONCAT() is incorrect. It concatenates offspring with a leading / for root nodes with ancestry IS NULL. Like '/1' instead of '1'. Use CONCAT_WS():

SELECT *
FROM   geolocations g
WHERE  EXISTS (
   SELECT FROM geolocations g2
   WHERE  g2.ancestry = CONCAT_WS('/', g.ancestry, g.id)  -- !
   )
    OR location_type = 2;

See:

• How to concatenate columns in a Postgres SELECT?

Still ugly. If you run queries like this a lot you might do more. If the table is read-only, add a boolean flag named has_children. Else consider a MATERIALIZED VIEW with that extra column or keep the table column current with triggers. Then your query can just be:

SELECT *
FROM   geolocations
WHERE  has_children
    OR location_type = 2;

has_children is typically not selective, so the query produces a lot of result rows and is never going to be very cheap (though a lot cheaper). Indexing the column won't help. We'd need complete information to maybe find a different angle. That's beyond the scope of this question.

Either way, if you need that redundant ancestry with every row, consider a proper array with a GIN index instead of the ugly string. Or maybe the additional module ltree, which is old, but for that purpose exactly.

Answer 2

PostgreSQL and many other RDBMSs often struggle with OR predicates.

What often happens, and has happened in this case, is that the compiler decides that it has no way of implementing the two OR conditions via a single seek, and instead scans the whole index, evaluating the two (or more) conditions on every row.

This is despite the more obvious (to a human) method of an Index Union.

What you are doing is a very common trick to help the compiler and force an Index Union. It is now evaluating the two sides entirely separately, and in this case it is much faster.

It may not always be faster, for example if location_type = 2 was a very large proportion of the table. The benefit is more obvious when the two conditions are very different in performance.

For example, WHERE id = @id OR someName = @name the first condition is a straight seek on a single row, whereas the second condition is a seek to a few rows. The compiler cannot satisfy this with a single seek, it therefore often jumps to scanning the whole table. An Index Union helps here because you can utilize an index on id and another index on someName

Answer 3

I get mostly the same speed for the EXISTS query either with or without the OR location_type = 2 as long as work_mem is set large enough (more than about 20MB).

For EXISTS without the OR clause, I get a Hash Join. With the OR clause I get Hashed SubPlan. The problem is that while Hash Join knows how to deal with low work_mem by spilling to disk in an efficient way, Hashed SubPlan does not. If work_mem is too low, it just changes to an unhashed SubPlan, which is slower.

The default setting of work_mem is way too small for a server running queries of this type.

Automatically converting to UNION or UNION ALL is tricky because it is difficult to prove that this gives the same answer as the OR. It is your prerogative to decide that you don't care if some apparent duplicates get removed, but is not the databases prerogative to make that decision for you.