Postgres uses usual array index instead of intarray index
I have the column agg_series_id with the type integer[] with intarray index created on it. Query for creating index is:
CREATE INDEX idx_agg_series_id ON some_tbl USING gin (agg_series_id gin__int_ops);
It is about 500k rows it the table. I have such execution plan for the query SELECT count(*) FROM some_tbl WHERE agg_series_id <@ ARRAY [1] :
Aggregate (cost=129835.91..129835.92 rows=1 width=8)
-> Seq Scan on some_tbl (cost=0.00..129835.89 rows=11 width=0)
Filter: (agg_series_id @> '{1}'::integer[])
As can be seen from the plan the intarray index is not used. Shutting down seqscan by setting set enable_seqscan=off; is not changed the plan. I tried to increase statistics for the column and tried to execute VACUUM ANALYZE .
But if I remove the intarray extension ( gin__int_ops ) from the index and then create a new index:
CREATE INDEX idx_agg_series_id2 ON some_tbl USING gin (agg_series_id);
In this case the new index is used in the execution plan:
Aggregate (cost=55.93..55.94 rows=1 width=8)
-> Bitmap Heap Scan on some_tbl (cost=12.08..55.91 rows=11 width=0)
Recheck Cond: (agg_series_id @> '{1}'::integer[])
-> Bitmap Index Scan on idx_agg_series_id2 (cost=0.00..12.08 rows=11 width=0)
Index Cond: (agg_series_id @> '{1}'::integer[])
So the question is: why the intarray index is not used in execution plan for integer[] column and the usual array index is used instead? Or maybe I can adjust some settings or types so the intarray index can be used?
PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
Do you specify the gin__int_ops option with some certain schema? Because it looks like you have the intarray extension installed into another schema. You see the <@ operator (along with && , @> ) of the intarray extension is equivalent to built-in operators of the same name so if there isn't the intarray extension installed, the <@ operator will be used as the operator of a regular array. In this turn the regular GIN index will be used.
You can check if there is the intarray extension installed in your scheme. Try to use the icount function in the some query. If you get an error with a 'not found' message so you don't have the intarray extension in the current schema. If so you can change the current schema with the command SET search_path TO some_another_schema .
Maybe there would be less confusion if a differing operator would be used for the intarray operations instead of a built-in one.
链接地址: http://www.djcxy.com/p/87234.html