Hi,
I’ve got a table with GIN index on integer[] type. While doing a query with filter criteria on that column has GIN index created, it’s not using index at all, still do the full table scan. Wondering why?
Table is analyzed.
dev=# \d+ booking_weekly
Table "booking_weekly"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+------------------------+-----------+----------+--------------+-------------
date | date | | plain | |
id | character varying(256) | | extended | |
t_wei | double precision | | plain | |
booking_ts | integer[] | | extended | |
Indexes:
"idx_booking_weekly_1_1" btree (id), tablespace "tbs_data"
"idx_booking_weekly_1_2" gin (booking_ts), tablespace "tbs_data"
dev=# select * from booking_weekly limit 1;
-[ RECORD 1
date | 2014-05-03
id | 148f8ecbf40
t_wei | 0.892571268670041
booking_ts | {2446685,4365133,5021137,2772581,1304970,6603422,262511,5635455,4637460,5250119,3037711,6273424,3198590,3581767,6612741,5813035,3074851}
dev=# explain analyze select * FROM booking_weekly
WHERE date = '2014-05-03' AND
booking_ts@>array[2446685];
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Seq Scan on booking_weekly (cost=10000000000.00..10000344953.64 rows=1288 width=1233) (actual time=0.015..1905.657 rows=1 loops=1)
-[ RECORD 2 ]--------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Filter: ((booking_ts @> '{2446685}'::integer[]) AND (date = '2014-05-03'::date))
-[ RECORD 3 ]--------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Rows Removed by Filter: 1288402
-[ RECORD 4 ]--------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Total runtime: 1905.687 ms
Thanks,
Suya
