pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: jacob@aers.ca
Cc: pgsql-sql@postgresql.org
Subject: Re: FW: Query length limitation in postgres server > 8.2.9
Date: Thu, 09 Jul 2009 14:43:15 -0400
Message-ID: <4566.1247164995@sss.pgh.pa.us> (raw)
In-Reply-To: <6B5AF6293A289F45826220B17ABE7937FDB1D3@BORON.aers.local>
References: <6B5AF6293A289F45826220B17ABE7937FDB16C@BORON.aers.local>
	<6B5AF6293A289F45826220B17ABE7937FDB1C0@BORON.aers.local>
	<3795.1247162732@sss.pgh.pa.us>
	<6B5AF6293A289F45826220B17ABE7937FDB1C6@BORON.aers.local>
	<3981.1247163226@sss.pgh.pa.us>
	<6B5AF6293A289F45826220B17ABE7937FDB1CB@BORON.aers.local>
	<4289.1247164139@sss.pgh.pa.us>
	<6B5AF6293A289F45826220B17ABE7937FDB1D3@BORON.aers.local>

<jacob@aers.ca> writes:
> the data is insert once, read many so we should be fine on that side.

It's not only the insert side where you pay for so many partial indexes.
On every query of the table, the planner is going to examine every one
of those indexes and determine whether the index is potentially usable.
Which means it attempts to prove the truth of the index WHERE clause
given the query WHERE conditions.  Most of those proof attempts are going
to fail for most queries, but not before eating a lot of planning
cycles.

On a read-mostly table, it'd probably be better to just eat the index
space needed to index the NULL entries.  They aren't going to affect
search speed noticeably for non-NULL entries, I think.

			regards, tom lane



view thread (13+ messages)

Message-ID: <4566.1247164995@sss.pgh.pa.us>
Permalink:  ../4566.1247164995@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/4566.1247164995@sss.pgh.pa.us

 · 

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-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, jacob@aers.ca
  Subject: Re: FW: Query length limitation in postgres server > 8.2.9
  In-Reply-To: <4566.1247164995@sss.pgh.pa.us>

* 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