Thread: GIN index not used

GIN index not used

From
"Huang, Suya"
Date:

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

Re: GIN index not used

From
Andreas Kretschmer
Date:
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°


Re: GIN index not used

From
Tom Lane
Date:
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


Re: GIN index not used

From
Andreas Kretschmer
Date:
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°


Re: GIN index not used

From
"Huang, Suya"
Date:
-----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
usinggin (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
seeyou'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.

Re: GIN index not used

From
Tom Lane
Date:
"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


Re: GIN index not used

From
"Huang, Suya"
Date:

-----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
revertedthat (commit 156475a589) because of backwards-compatibility worries.  It doesn't look like anything got done
aboutit 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
hashjoins. 

            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


Re: GIN index not used

From
Emre Hasegeli
Date:
> -----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.