Re: How to hint two columns IS NOT DISTINCT FROM each other in a join - Mailing list pgsql-general
From | Kim Rose Carlsen |
---|---|
Subject | Re: How to hint two columns IS NOT DISTINCT FROM each other in a join |
Date | |
Msg-id | AM4PR0501MB26105A99BC9DF98CEF5967D0C7AD0@AM4PR0501MB2610.eurprd05.prod.outlook.com Whole thread Raw |
In response to | How to hint two columns IS NOT DISTINCT FROM each other in a join (Kim Rose Carlsen <krc@hiper.dk>) |
List | pgsql-general |
Sorry for double post, just ignore this post..
Sent: Thursday, October 27, 2016 6:34:58 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] How to hint two columns IS NOT DISTINCT FROM each other in a join
This sender failed our fraud detection checks and may not be who they appear to be. Learn about spoofing | Feedback |
Hi
I was wondering if there is a way to hint that two columns in two different tables IS NOT DISTINCT FROM each other. So that the optimizer may assume if table_a.key = 'test' THEN table_b.key = 'test' .
The equals operator already does this but it does not handle NULLS very well (or not at all). And it seems IS NOT DISTINCT FROM is not indexable and doesn't establish the same inference rules as equals.
Example:
CREATE TABLE a (
id INTEGER PRIMARY KEY,
key VARCHAR,
value VARCHAR
);
CREATE INDEX ON a (key);
INSERT INTO a
VALUES (1, 'test', 'test'), (2, 'foo', 'bar'), (3, null, null), (4, 'baz', 'qoz');
CREATE VIEW view_a AS (
SELECT table_a.id,
table_a.key,
table_a.value,
table_b.key as b_key
FROM a AS table_a
JOIN a AS table_b
ON table_a.id = table_b.id
);
CREATE VIEW view_a_eq AS (
SELECT table_a.id,
table_a.key,
table_a.value,
table_b.key as b_key
FROM a AS table_a
JOIN a AS table_b
ON table_a.id = table_b.id
AND table_a.key = table_b.key
);
CREATE VIEW view_a_distinct AS (
SELECT table_a.id,
table_a.key,
table_a.value,
table_b.key as b_key
FROM a AS table_a
JOIN a AS table_b
ON table_a.id = table_b.id
AND table_a.key IS NOT DISTINCT FROM table_b.key
);
We only get index scan on table_a
EXPLAIN SELECT * FROM view_a_eq WHERE key = 'test';
EXPLAIN SELECT * FROM view_a_distinct WHERE key = 'test';
pgsql-general by date: