GIN index not used

From: "Huang, Suya" <Suya(dot)Huang(at)au(dot)experian(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: GIN index not used
Date: 2014-07-11 04:14:38
Message-ID: D83E55F5F4D99B4A9B4C4E259E6227CD014C3263@AUX1EXC01.apac.experian.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Andreas Kretschmer 2014-07-11 04:44:52 Re: GIN index not used
Previous Message Mark Kirkwood 2014-07-11 00:40:15 Re: 60 core performance with 9.3