Обсуждение: BUG #12888: Ignoring OPERATOR::RESTRICT=eqsel with subquery (pgsql-performance)
BUG #12888: Ignoring OPERATOR::RESTRICT=eqsel with subquery (pgsql-performance)
От
aleksmstu@mail.ru
Дата:
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!