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!
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) |