BUG #12888: Ignoring OPERATOR::RESTRICT=eqsel with subquery (pgsql-performance)

From: aleksmstu(at)mail(dot)ru
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #12888: Ignoring OPERATOR::RESTRICT=eqsel with subquery (pgsql-performance)
Date: 2015-03-21 20:51:12
Message-ID: 20150321205112.2576.67653@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 12888
Logged by: Aleksandr Utorov
Email address: aleksmstu(at)mail(dot)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!

Browse pgsql-bugs by date

  From Date Subject
Next Message herzir232 2015-03-21 22:04:51 BUG #12889: Documentation
Previous Message Michael Paquier 2015-03-21 13:00:42 Re: Re: [BUGS] BUG #11805: Missing SetServiceStatus call during service shutdown in pg_ctl (Windows only)