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