Jacques Caron <jc at oxado.com> writes:

> Hi all,
>
> I'm trying to figure out the need for all the indexes that are created
> on the sl_log_* tables... They seem redudant to me:
>
>     "PartInd_ad_sl_log_1-node-1" btree (log_xid _ad.xxid_ops) WHERE
> log_origin = 1
>     "PartInd_ad_sl_log_1-node-3" btree (log_xid _ad.xxid_ops) WHERE
> log_origin = 3
>     "PartInd_ad_sl_log_1-node-8" btree (log_xid _ad.xxid_ops) WHERE
> log_origin = 8
>     "sl_log_1_idx1" btree (log_origin, log_xid _ad.xxid_ops, log_actionseq)
>
> Obviously, to make an index lookup based on log_origin and log_xid,
> one can either use the appropriate partial index or the main index,
> and they would seem to me to do exactly the same thing (there's just
> the little difference with the log_actionseq column). So either the
> partial indexes are really not that useful (my opinion), or the full
> index is a leftover from previous versions, is now obsolete and should
> be removed?
>
> Did I miss something?

The introduction of these partial indices dates back to the following
discussion thread on pgsql-hackers:
http://archives.postgresql.org/pgsql-hackers/2006-06/msg01516.php

At one point, we had this as a second index:
 create index sl_log_1_idx2 on @NAMESPACE at .sl_log_1
	(log_xid @NAMESPACE at .xxid_ops);

Unfortunately, it was apparently leading to problems in that data
sourced from different origins might have xxid values of varying sign.

So, in lieu of that, I introduced code that would generate a
per-origin partial index, which would necessarily not suffer from the
rollover problem that sl_log_1_idx2 would run into.

It is quite likely that the partial indices will be preferred, as the
first column in sl_log_1_idx1 doesn't discriminate much.
-- 
let name="cbbrowne" and tld="linuxdatabases.info" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/linuxxian.html
"Those who doubt the importance  of  a convenient notation should  try
writing a LISP interpreter in COBOL  or doing long division with Roman
numerals." -- Hal Fulton