Message-ID: <50DC27D6.10608@richardneill.org>
Date: Thu, 27 Dec 2012 10:49:58 +0000
From: Richard Neill <rjn@richardneill.org>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:16.0) Gecko/20121010 Thunderbird/16.0.1
MIME-Version: 1.0
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Why does the query planner use two full indexes, when
	a dedicated partial index exists?
References: <50D22DE2.4090301@richardneill.org>
	<28778.1355972790@sss.pgh.pa.us>
	<50D3CAC4.3070800@richardneill.org>
	<CAMkU=1w1yqYepDpmKo5fuL9Akkk4t_AOiwoc6u2k5ysfAEt1kQ@mail.gmail.com>
	<50D5EDDF.6010702@richardneill.org>
	<CAMkU=1xhiU5jUvirUnky4G9LGvSdLFrSaZ53EH7_WC722txLRw@mail.gmail.com>
In-Reply-To: 
 <CAMkU=1xhiU5jUvirUnky4G9LGvSdLFrSaZ53EH7_WC722txLRw@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


>         The partial index is highly leveraged.  If every tuple in the
>         table is updated once, that amounts to every tuple in the index
 >         being updated 25,000 times.
>
>     How so? That sounds like O(n_2) behaviour.
>
> If the table has 5 million rows while the index has 200 (active) rows at
> any given time, then to update every row in the table to null and back
> again would be 100% turn over of the table.  But each such change would
> lead to an addition and then a deletion from the index.  So 100%
> turnover of the table would be a 5 million / 200 = 25,000 fold turn of
> the index.

Sorry, I was being dense. I misread that as:
    "every time a single tuple in the table is updated, the entire index
     (every row) is updated".
Yes, of course your explanation makes sense.

>
> There is some code that allows a btree index entry to get killed (and so
> the slot to be reused) without any vacuum, if a scan follows that entry
> and finds the corresponding tuple in the table no longer visible to
> anyone.  I have not examined this code, and don't know whether it is
> doing its job but just isn't enough to prevent the bloat, or if for some
> reason it is not applicable to your situation.
>

It looks like my solution is going to be a REINDEX invoked from cron, or 
maybe just every 100k inserts.


In terms of trying to improve this behaviour for other PG users in the 
future, are there any more diagnostics I can do for you? Having found a 
special case, I'd like to help permanently resolve it if I can.


Thanks very much again.

Best wishes,

Richard


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance