pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Stark <gsstark@mit.edu>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Greg Stark <gsstark@mit.edu>
Cc: Ross J. Reedstrom <reedstrm@rice.edu>
Cc: Merlin Moncure <merlin.moncure@rcsonline.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: arrays and indexes
Date: 27 Jul 2004 00:32:37 -0400
Message-ID: <87ekmyul22.fsf@stark.xeocode.com> (raw)
In-Reply-To: <9847.1090876872@sss.pgh.pa.us>
References: <6EE64EF3AB31D5448D0007DD34EEB34101AEF1@Herge.rcsinc.local>
	<20040726045710.GA14892@cnx.rice.edu>
	<87acxnwaev.fsf@stark.xeocode.com>
	<20040726184703.GA27820@cnx.rice.edu>
	<87y8l6v6wv.fsf@stark.xeocode.com>
	<9847.1090876872@sss.pgh.pa.us>


Tom Lane <tgl@sss.pgh.pa.us> writes:

> > I still don't really know why it failed, but after two days building the
> > index I gave up.
> 
> Sounds like a bug to me.  Could you put together a test case?

At the time I contacted one of the GiST authors and we went over things for a
while. They diagnosed the problem as being caused by having a poor selectivity
GiST btree as the leading column in the index.

He seemed to think this was fairly fundamental and wasn't something they were
going to be able to address. And I was fairly certain I didn't want to turn
the index upside down to have the more selective columns first (as is usually
normal) for various reasons.

So I gave it up as a lost cause. In any case in my application it was unlikely
to really help. I expect that leading btree index to narrow the search to only
a few hundred or few thousand records in the normal case. So the access times
are already within reason even having to dig through all the records. And
since other queries are likely to need other records from that set I'll need
them all in cache eventually. There are a lot of array columns to search
through, so the added i/o to read all those indexes would probably be a net
loss when they push other things out of cache.

I could try setting up a test case, but I think all it took was having a
btree-gist index that was insufficiently selective. In my case I had about 900
integer values each on the order of 100-1000 records.

-- 
greg




view thread (8+ messages)

Message-ID: <87ekmyul22.fsf@stark.xeocode.com>
Permalink:  ../87ekmyul22.fsf@stark.xeocode.com/
Also on:    postgresql.org/message-id/87ekmyul22.fsf@stark.xeocode.com

 · 

reply

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: gsstark@mit.edu, tgl@sss.pgh.pa.us, reedstrm@rice.edu, merlin.moncure@rcsonline.com
  Subject: Re: arrays and indexes
  In-Reply-To: <87ekmyul22.fsf@stark.xeocode.com>

* 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