From: Richard Neill <rn214@richardneill.org>
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?
Date: Thu, 27 Dec 2012 10:52:01 +0000
Message-ID: <50DC2851.3000001@richardneill.org> (raw)
In-Reply-To: <CAMkU=1xhiU5jUvirUnky4G9LGvSdLFrSaZ53EH7_WC722txLRw@mail.gmail.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
> 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
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: rn214@richardneill.org
Subject: Re: Why does the query planner use two full indexes, when a dedicated partial index exists?
In-Reply-To: <50DC2851.3000001@richardneill.org>
* 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