Re: GIN index not used

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