Thread: BUG #12871: Bug in intarray extension operators (using generic selectivity estimators instead of array oriented)
BUG #12871: Bug in intarray extension operators (using generic selectivity estimators instead of array oriented)
From
maxim.boguk@gmail.com
Date:
The following bug has been logged on the website: Bug reference: 12871 Logged by: Maksym Boguk Email address: maxim.boguk@gmail.com PostgreSQL version: 9.4.1 Operating system: Linux Description: As my bugreport BUG #12862 stalled in queue for unknown reason I'll try post it again. First test case showing issue: postgres=# create database test; CREATE DATABASE postgres=# \c test You are now connected to database "test" as user "postgres". test=# create table test as select array[100]::integer[] as f1 from generate_series(1,10000); SELECT 10000 test=# analyze test; ANALYZE test=# explain analyze select * from test where f1 && array[100]; QUERY PLAN --------------------------------------------------------------------------------------------------------- Seq Scan on test (cost=0.00..532.40 rows=10000 width=25) (actual time=0.048..6.207 rows=10000 loops=1) Filter: (f1 && '{100}'::integer[]) Total runtime: 7.154 ms (3 rows) test=# create extension intarray; CREATE EXTENSION test=# explain analyze select * from test where f1 && array[100]; QUERY PLAN ------------------------------------------------------------------------------------------------------ Seq Scan on test (cost=0.00..532.40 rows=10 width=25) (actual time=0.062..5.598 rows=10000 loops=1) Filter: (f1 && '{100}'::integer[]) Total runtime: 6.548 ms (3 rows) As you could see after installing the intarray extension PostgreSQL started use a general selectivity estimator. Estimated rows=10000 before create extension and estimated rows=10 after, 10000 - correct one. Problem that custom (redefined) intarray && @> <@ operators are defined with contsel/contjoinsel instead of arraycontsel/arraycontjoinsel. However, simple change extension/intarray--1.0.sql with correct CREATE OPERATOR doesn't fix issue: test=# explain analyze select * from test where f1 && array[100]; ERROR: arraycontsel called for unrecognized operator 814221170 I cannot find any quick fix for this issue because OID of custom && operator isn't fixed, so no simple change of arraycontsel/arraycontjoinsel possible. An issue exists in any version since introducing arraycontsel/arraycontjoinsel in 9.2 up to HEAD.