|
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 |
