The following bug has been logged on the website:
Bug reference: 12888
Logged by: Aleksandr Utorov
Email address: aleksmstu@mail.ru
PostgreSQL version: 9.4.1
Operating system: Win x86-32
Description:
Hello,
i create the operator ===
but the planner uses an index for a simple query only (e.g. SELECT * FROM t
WHERE id === 1 )
In subquery the OPERATOR::RESTRICT=eqsel is ignoring, i.e no index used
(e.g. SELECT * FROM t WHERE id === (SELECT 1) )
-- Full code
--
-- Create the operator === with RESTRICT=eqsel
CREATE FUNCTION is_not_distinct_from(anyelement, anyelement) RETURNS bool AS
'SELECT $1=$2 OR ($1 IS NULL AND $2 IS NULL);' LANGUAGE sql IMMUTABLE;
CREATE OPERATOR === ( PROCEDURE=is_not_distinct_from(anyelement,anyelement),
LEFTARG=anyelement, RIGHTARG=anyelement, COMMUTATOR= ===
,RESTRICT=eqsel, JOIN=eqjoinsel, HASHES, MERGES);
-- Create a test table.
CREATE TABLE t (
id integer NOT NULL,
CONSTRAINT t_pkey PRIMARY KEY (id)
);
INSERT INTO t(id) SELECT generate_series(1, 100000);
-- Test 1.
SELECT * FROM t WHERE id === 1;
'Index Only Scan using t_pkey on t (cost=0.29..8.31 rows=1 width=4)'
' Index Cond: (id = 1)'
-- OK (the best performance)
-- Test 2.
SELECT * FROM t WHERE id === ANY(ARRAY[1]);
'Seq Scan on t (cost=0.00..13943.00 rows=1 width=4)'
' Filter: (id === ANY ('{1}'::integer[]))'
-- Low performace!
-- Test 3.
SELECT * FROM t WHERE id === (SELECT 1);
'Seq Scan on t (cost=0.01..26443.01 rows=1 width=8)'
' Filter: (id === $0)'
' InitPlan 1 (returns $0)'
' -> Result (cost=0.00..0.01 rows=1 width=0)'
--Low performace!