pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedGIN index not used
8+ messages / 4 participants
[nested] [flat]
* GIN index not used
@ 2014-07-11 04:14 Huang, Suya <Suya.Huang@au.experian.com>
2014-07-11 04:44 ` Re: GIN index not used Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 1 reply; 8+ messages in thread
From: Huang, Suya @ 2014-07-11 04:14 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: GIN index not used
2014-07-11 04:14 GIN index not used Huang, Suya <Suya.Huang@au.experian.com>
@ 2014-07-11 04:44 ` Andreas Kretschmer <akretschmer@spamfence.net>
2014-07-11 04:56 ` Re: GIN index not used Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Andreas Kretschmer @ 2014-07-11 04:44 UTC (permalink / raw)
To: 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°
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: GIN index not used
2014-07-11 04:14 GIN index not used Huang, Suya <Suya.Huang@au.experian.com>
2014-07-11 04:44 ` Re: GIN index not used Andreas Kretschmer <akretschmer@spamfence.net>
@ 2014-07-11 04:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-07-11 05:01 ` Re: GIN index not used Andreas Kretschmer <akretschmer@spamfence.net>
2014-07-11 05:26 ` Re: GIN index not used Huang, Suya <Suya.Huang@au.experian.com>
0 siblings, 2 replies; 8+ messages in thread
From: Tom Lane @ 2014-07-11 04:56 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@spamfence.net>; +Cc: Huang, Suya <Suya.Huang@au.experian.com>; pgsql-performance
Andreas Kretschmer <akretschmer@spamfence.net> writes:
> Huang, Suya <Suya.Huang@au.experian.com> wrote:
>> 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?
> Btw.: works for me:
Yeah, me too:
regression=# create table booking_weekly(booking_ts int[]);
CREATE TABLE
regression=# create index on booking_weekly using gin (booking_ts);
CREATE INDEX
regression=# explain select * from booking_weekly where booking_ts@>array[2446685];
QUERY PLAN
--------------------------------------------------------------------------------------------
Bitmap Heap Scan on booking_weekly (cost=8.05..18.20 rows=7 width=32)
Recheck Cond: (booking_ts @> '{2446685}'::integer[])
-> Bitmap Index Scan on booking_weekly_booking_ts_idx (cost=0.00..8.05 rows=7 width=0)
Index Cond: (booking_ts @> '{2446685}'::integer[])
Planning time: 0.862 ms
(5 rows)
What PG version is this? What non-default planner parameter settings are
you using? (Don't say "none", because I can see you've got enable_seqscan
turned off.)
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: GIN index not used
2014-07-11 04:14 GIN index not used Huang, Suya <Suya.Huang@au.experian.com>
2014-07-11 04:44 ` Re: GIN index not used Andreas Kretschmer <akretschmer@spamfence.net>
2014-07-11 04:56 ` Re: GIN index not used Tom Lane <tgl@sss.pgh.pa.us>
@ 2014-07-11 05:01 ` Andreas Kretschmer <akretschmer@spamfence.net>
1 sibling, 0 replies; 8+ messages in thread
From: Andreas Kretschmer @ 2014-07-11 05:01 UTC (permalink / raw)
To: pgsql-performance
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> What PG version is this? What non-default planner parameter settings are
> you using? (Don't say "none", because I can see you've got enable_seqscan
> turned off.)
LOL, right ;-)
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: GIN index not used
2014-07-11 04:14 GIN index not used Huang, Suya <Suya.Huang@au.experian.com>
2014-07-11 04:44 ` Re: GIN index not used Andreas Kretschmer <akretschmer@spamfence.net>
2014-07-11 04:56 ` Re: GIN index not used Tom Lane <tgl@sss.pgh.pa.us>
@ 2014-07-11 05:26 ` Huang, Suya <Suya.Huang@au.experian.com>
2014-07-11 05:43 ` Re: GIN index not used Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 8+ messages in thread
From: Huang, Suya @ 2014-07-11 05:26 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Andreas Kretschmer <akretschmer@spamfence.net>; +Cc: pgsql-performance
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, July 11, 2014 2:56 PM
To: Andreas Kretschmer
Cc: Huang, Suya; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] GIN index not used
Andreas Kretschmer <akretschmer@spamfence.net> writes:
> Huang, Suya <Suya.Huang@au.experian.com> wrote:
>> 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?
> Btw.: works for me:
Yeah, me too:
regression=# create table booking_weekly(booking_ts int[]); CREATE TABLE regression=# create index on booking_weekly using gin (booking_ts); CREATE INDEX regression=# explain select * from booking_weekly where booking_ts@>array[2446685];
QUERY PLAN
--------------------------------------------------------------------------------------------
Bitmap Heap Scan on booking_weekly (cost=8.05..18.20 rows=7 width=32)
Recheck Cond: (booking_ts @> '{2446685}'::integer[])
-> Bitmap Index Scan on booking_weekly_booking_ts_idx (cost=0.00..8.05 rows=7 width=0)
Index Cond: (booking_ts @> '{2446685}'::integer[]) Planning time: 0.862 ms
(5 rows)
What PG version is this? What non-default planner parameter settings are you using? (Don't say "none", because I can see you've got enable_seqscan turned off.)
regards, tom lane
Just found out something here http://www.postgresql.org/message-id/17021.1234474178@sss.pgh.pa.us
So I dropped the index and recreate it by specifying: using gin(terms_ts gin__int_ops) and the index works.
My PG version is 9.3.4, none-default planner settings:
enable_mergejoin = off
enable_nestloop = off
enable_seqscan is turned off for session while trying to figure out why the GIN index is not used.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: GIN index not used
2014-07-11 04:14 GIN index not used Huang, Suya <Suya.Huang@au.experian.com>
2014-07-11 04:44 ` Re: GIN index not used Andreas Kretschmer <akretschmer@spamfence.net>
2014-07-11 04:56 ` Re: GIN index not used Tom Lane <tgl@sss.pgh.pa.us>
2014-07-11 05:26 ` Re: GIN index not used Huang, Suya <Suya.Huang@au.experian.com>
@ 2014-07-11 05:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-07-11 05:47 ` Re: GIN index not used Huang, Suya <Suya.Huang@au.experian.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2014-07-11 05:43 UTC (permalink / raw)
To: Huang, Suya <Suya.Huang@au.experian.com>; +Cc: Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
"Huang, Suya" <Suya.Huang@au.experian.com> writes:
> Just found out something here http://www.postgresql.org/message-id/17021.1234474178@sss.pgh.pa.us
> So I dropped the index and recreate it by specifying: using gin(terms_ts gin__int_ops) and the index works.
Oh, you're using contrib/intarray?
Pursuant to the thread you mention above, we removed intarray's <@ and @>
operators (commit 65e758a4d3) but then reverted that (commit 156475a589)
because of backwards-compatibility worries. It doesn't look like anything
got done about it since then. Perhaps the extension upgrade
infrastructure would offer a solution now.
> My PG version is 9.3.4, none-default planner settings:
> enable_mergejoin = off
> enable_nestloop = off
[ raised eyebrow... ] It's pretty hard to see how those would be
a good idea. Not all problems are best solved by hash joins.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: GIN index not used
2014-07-11 04:14 GIN index not used Huang, Suya <Suya.Huang@au.experian.com>
2014-07-11 04:44 ` Re: GIN index not used Andreas Kretschmer <akretschmer@spamfence.net>
2014-07-11 04:56 ` Re: GIN index not used Tom Lane <tgl@sss.pgh.pa.us>
2014-07-11 05:26 ` Re: GIN index not used Huang, Suya <Suya.Huang@au.experian.com>
2014-07-11 05:43 ` Re: GIN index not used Tom Lane <tgl@sss.pgh.pa.us>
@ 2014-07-11 05:47 ` Huang, Suya <Suya.Huang@au.experian.com>
2014-07-12 08:31 ` Re: GIN index not used Emre Hasegeli <emre@hasegeli.com>
0 siblings, 1 reply; 8+ messages in thread
From: Huang, Suya @ 2014-07-11 05:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, July 11, 2014 3:43 PM
To: Huang, Suya
Cc: Andreas Kretschmer; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] GIN index not used
"Huang, Suya" <Suya.Huang@au.experian.com> writes:
> Just found out something here
> http://www.postgresql.org/message-id/17021.1234474178@sss.pgh.pa.us
> So I dropped the index and recreate it by specifying: using gin(terms_ts gin__int_ops) and the index works.
Oh, you're using contrib/intarray?
Pursuant to the thread you mention above, we removed intarray's <@ and @> operators (commit 65e758a4d3) but then reverted that (commit 156475a589) because of backwards-compatibility worries. It doesn't look like anything got done about it since then. Perhaps the extension upgrade infrastructure would offer a solution now.
> My PG version is 9.3.4, none-default planner settings:
> enable_mergejoin = off
> enable_nestloop = off
[ raised eyebrow... ] It's pretty hard to see how those would be a good idea. Not all problems are best solved by hash joins.
regards, tom lane
About the contrib/intarray, do I have other choices not using that one?
About the join, yeah, in our testing for DW-like queries, hash join does improved the performance greatly...
Thanks,
Suya
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: GIN index not used
2014-07-11 04:14 GIN index not used Huang, Suya <Suya.Huang@au.experian.com>
2014-07-11 04:44 ` Re: GIN index not used Andreas Kretschmer <akretschmer@spamfence.net>
2014-07-11 04:56 ` Re: GIN index not used Tom Lane <tgl@sss.pgh.pa.us>
2014-07-11 05:26 ` Re: GIN index not used Huang, Suya <Suya.Huang@au.experian.com>
2014-07-11 05:43 ` Re: GIN index not used Tom Lane <tgl@sss.pgh.pa.us>
2014-07-11 05:47 ` Re: GIN index not used Huang, Suya <Suya.Huang@au.experian.com>
@ 2014-07-12 08:31 ` Emre Hasegeli <emre@hasegeli.com>
0 siblings, 0 replies; 8+ messages in thread
From: Emre Hasegeli @ 2014-07-12 08:31 UTC (permalink / raw)
To: Huang, Suya <Suya.Huang@au.experian.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
> -----Original Message-----
It is hard to read your message. You should indicate the quoted lines.
Please fix your email client.
> About the contrib/intarray, do I have other choices not using that one?
integer[] and contrib/intarray are two different data types.
> About the join, yeah, in our testing for DW-like queries, hash join does improved the performance greatly...
Then, it should be chosen by the planner. I doubt it is the best
choice in all cases. It is not advised to set these parameters
globally.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2014-07-12 08:31 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-07-11 04:14 GIN index not used Huang, Suya <Suya.Huang@au.experian.com>
2014-07-11 04:44 ` Andreas Kretschmer <akretschmer@spamfence.net>
2014-07-11 04:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-07-11 05:01 ` Andreas Kretschmer <akretschmer@spamfence.net>
2014-07-11 05:26 ` Huang, Suya <Suya.Huang@au.experian.com>
2014-07-11 05:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-07-11 05:47 ` Huang, Suya <Suya.Huang@au.experian.com>
2014-07-12 08:31 ` Emre Hasegeli <emre@hasegeli.com>
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