Thread: BUG #12862: Bug in intarray extension operators (using generic selectivity estimators instead of array oriented)

The following bug has been logged on the website:

Bug reference:      12862
Logged by:          Maksym Boguk
Email address:      maxim.boguk@postgresql-consulting.com
PostgreSQL version: 9.2.9
Operating system:   Linux
Description:

Hi,

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.

On Fri, Mar 13, 2015 at 02:54:56PM +0000, maxim.boguk@postgresql-consulting.com wrote:
> 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.

Yeah, I am not sure what to recommend here as our custom type optimizer
statistics are not as good as the built-in ones.  Did you try running
ANALYZE?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +