Re: [PERFORM] GIN index not used if created in the same transaction as query

Adam Brusselback writes: > Is there any easy way I can know if an index is usable or not? Are there > any catalog views or anything I could check that in? IIRC, you can look at pg_index.indcheckxmin --- if that's set, then the index had broken HOT chains during

Re: [PERFORM] GIN index not used if created in the same transaction as query

> > Does the "multiple steps" part involve UPDATEs on pre-existing rows? > Do the updates change the column(s) used in the gin index? > Yes they do, however the updates happen prior to the index creation. I just tried, and that looks like the solution. I really appreciate your help on this.

Re: [PERFORM] GIN index not used if created in the same transaction as query

Adam Brusselback writes: > I have a function which builds two temp tables, fills each with data (in > multiple steps), creates a gin index on one of the tables, analyzes each > table, then runs a query joining the two. > My issue is, I am getting inconsistent results

[PERFORM] GIN index not used if created in the same transaction as query

Hey all, first off, i'm running: PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit At the high level, I am having an issue with a query not using an index, and in a very hard to reproduce way. I have a function which builds two temp tables, fills each with

Re: [PERFORM] GIN index not used

-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

[PERFORM] GIN index not used

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

Re: [PERFORM] GIN index not used

Huang, Suya suya.hu...@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

Re: [PERFORM] GIN index not used

Andreas Kretschmer akretsch...@spamfence.net writes: Huang, Suya suya.hu...@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.

Re: [PERFORM] GIN index not used

Tom Lane t...@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

Re: [PERFORM] GIN index not used

-Original Message- From: Tom Lane [mailto:t...@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 akretsch...@spamfence.net writes: Huang, Suya suya.hu

Re: [PERFORM] GIN index not used

Huang, Suya suya.hu...@au.experian.com writes: Just found out something here http://www.postgresql.org/message-id/17021.1234474...@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?

Re: [PERFORM] GIN index not used

-Original Message- From: Tom Lane [mailto:t...@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.hu...@au.experian.com writes: Just found out something