5

Imagine a view which contains a COALESCE of two varchar columns from two different tables.

The underlying varchars are indexed in both tables.

In Postgres 11.6, filtering this view on the result of the COALESCE doesn't use the indexes, instead doing a table scan.

But if I change the columns to text, the very same view filtered on the same column, the index is used as you'd expect.

Example

Suppose I have a table of measured values for some identifiers over time. There's also an almost identical table containing estimated values:

CREATE TABLE measured (
  id int,
  ts timestamp,
  identifier character varying,
  measured_value int
);
CREATE INDEX ON measured(identifier);

CREATE TABLE estimated (
  id int,
  ts timestamp,
  identifier character varying,
  estimated_value int
);
CREATE INDEX ON estimated(identifier);

Each table has 1 million rows of data in:

INSERT INTO measured
SELECT
    generate_series(1, 1000000),
    to_timestamp((random() * 100000)::int),
    left(md5(random()::text), 2),
    random() * 10;

INSERT INTO estimated
SELECT
    generate_series(1, 1000000),
    to_timestamp((random() * 100000)::int),
    left(md5(random()::text), 2),
    random() * 10;

Filtering on the identifier column in either table alone uses the index as we'd expect.

We have a view which returns all the measured data, and all the estimated data, combining into a single row where the identifier and timestamp are the same:

CREATE VIEW combined AS
SELECT
    COALESCE(measured.ts, estimated.ts) AS ts,
    COALESCE(measured.identifier, estimated.identifier) AS identifier,
    measured_value,
    estimated_value
FROM measured
FULL OUTER JOIN estimated ON measured.identifier = estimated.identifier
                         AND measured.ts = estimated.ts;

This combined view performs a table scan when filtered on the identifier column:

EXPLAIN ANALYZE
SELECT * FROM combined
WHERE identifier = 'ab';

But if we change the data type of identifier to text, filtering the view uses the index as expected.

Is this a bug?

For convenience, I've pasted the whole script here

1 Answer 1

3

I can confirm the problem. Even in Postgres 13. Even after:

SET enable_seqscan = off;

... which forces the index if at all possible. To be precise, indexes are used, but as full index scans - a measure of last resort to avoid sequential scans. The telltale whether indexes are used meaningfully is an index condition instead of a filter in the EXPLAIN output. Like:

Index Cond: (identifier = 'ab'::text)

Instead of:

Filter: ((COALESCE(measured.identifier, estimated.identifier))::text = 'ab'::text)

The index scan alone, without index condition, "uses" the index but not in a useful manner:

->  Index Scan using measured_identifier_idx on measured 

Simpler query

For starters, let's simplify the base query with the USING clause in the join condition:

CREATE VIEW combined AS
SELECT ts
     , identifier
     , m.measured_value
     , e.estimated_value
FROM   measured m
FULL   JOIN estimated e USING (identifier, ts);

Equivalent, except that the join columns identifier and ts are only added once to the list of output columns, which can only be beneficial, especially since SELECT * now delivers unique column names.

But it does nothing for the index issue.

Research

It certainly has to do with the fact that text is "preferred" among string types. varchar is binary compatible with text, basically just an alias, or rather, varchar is something like a domain with base type text. Indexes on varchar columns use the type text internally.

This makes Postgres add a cast to text when using the index (which is a no-op, really). Becomes evident in the EXPLAIN output, even for the simplest query:

EXPLAIN SELECT * FROM combined WHERE identifier = 'ab';

...
Index Cond: ((identifier)::text = 'ab'::text)
...

This no-op of casting to text becomes an obstacle along the way. Not sure where the query planner loses confidence that the index is applicable anyway. Using text from the get-go obviously removes the problem.

Views are implemented with a query rewrite rules. I think we can rule out any involvement of the rule system. I can reproduce the problem with the query alone, without VIEW (in Postgres 13):

db<>fiddle here

Again, indexes are used with data type text:

db<>fiddle here

But only with COALESCE and FULL OUTER JOIN.

To make sure, I tested the same scenario with an integer column. Same results as with text:

db<>fiddle here

Conclusion

COALESCE in combination with FULL OUTER JOIN seems to get special treatment from the query planner, so that indexes are applicable. It's used in the expression to compute the output column identifier, explicitly in your original or implicitly in my simplified query. And that seems to match how the join condition of a FULL OUTER JOIN is implemented internally. This falls flat for some reason when varchar is used - most likely because of the added (logically irrelevant) cast to ::text.

1
  • Do you think that this qualifies as a bug? Should I add to a bug tracker somewhere?
    – LondonRob
    Aug 6, 2021 at 10:34

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.