From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-performance@postgresql.org
Subject: Re: GIN index not used
Date: Fri, 11 Jul 2014 06:44:52 +0200
Message-ID: <20140711044452.GA7390@tux> (raw)
In-Reply-To: <D83E55F5F4D99B4A9B4C4E259E6227CD014C3263@AUX1EXC01.apac.experian.local>
References: <D83E55F5F4D99B4A9B4C4E259E6227CD014C3263@AUX1EXC01.apac.experian.local>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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°
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: akretschmer@spamfence.net
Subject: Re: GIN index not used
In-Reply-To: <20140711044452.GA7390@tux>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox