Re: GIN index not used - Mailing list pgsql-performance

From Andreas Kretschmer
Subject Re: GIN index not used
Date
Msg-id 20140711044452.GA7390@tux
Whole thread Raw
In response to GIN index not used  ("Huang, Suya" <Suya.Huang@au.experian.com>)
Responses Re: GIN index not used  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
Huang, Suya <Suya.Huang@au.experian.com> wrote:

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

Try to add an index on the date-column.

Btw.: works for me:

,----
| test=*# \d foo;
|        Table "public.foo"
|  Column |   Type    | Modifiers
| --------+-----------+-----------
|  id     | integer   |
|  ts     | integer[] |
| Indexes:
|     "idx_foo" gin (ts)
|
| test=*# set enable_seqscan to off;
| SET
| Time: 0,049 ms
| test=*# select * from foo;
|  id |     ts
| ----+------------
|   1 | {1,2,3}
|   2 | {10,20,30}
| (2 rows)
|
| Time: 0,230 ms
| test=*# explain select * from foo where ts @> array[2];
|                               QUERY PLAN
| ----------------------------------------------------------------------
|  Bitmap Heap Scan on foo  (cost=8.00..12.01 rows=1 width=36)
|    Recheck Cond: (ts @> '{2}'::integer[])
|    ->  Bitmap Index Scan on idx_foo  (cost=0.00..8.00 rows=1 width=0)
|          Index Cond: (ts @> '{2}'::integer[])
| (4 rows)
`----


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°


pgsql-performance by date:

Previous
From: "Huang, Suya"
Date:
Subject: GIN index not used
Next
From: Tom Lane
Date:
Subject: Re: GIN index not used