Re: Multi-column indexes

Hi Tom!

Yep, there are a large number of host_luid/log_luid combinations (there
are approximatly 5-10 hosts and 1-3 logs per system we are running).

Thanks for the recommended workaround, I'll have a try at it at some point
tomorrow.

Regards!
Ed

On Sat, 15 Jan 2005, Tom Lane wrote:

> Edmund Dengler <edmundd(at)eSentire(dot)com> writes:
> > "record_to_process_idx" unique, btree (host_luid, log_luid, luid) WHERE (error IS NULL)
>
> > explain analyze
> > select record
> > from agent.record
> > where host_luid = 3::bigint
> > and log_luid = 2::bigint
> > and error is null
> > order by host_luid desc, log_luid desc, luid desc
> > limit 1
>
> > Limit (cost=0.00..1.47 rows=1 width=286) (actual time=249064.949..249064.950 rows=1 loops=1)
> > -> Index Scan Backward using record_to_process_idx on record (cost=0.00..13106.73 rows=8898 width=286) (actual time=249064.944..249064.944 rows=1 loops=1)
> > Index Cond: ((host_luid = 3::bigint) AND (log_luid = 2::bigint))
> > Filter: (error IS NULL)
> > Total runtime: 249065.004 ms
>
> Are there a whole lotta rows with that host_luid/log_luid combination?
>
> What's happening is that the index search initially finds the first such
> row, and then it has to step to the last such row to start the backwards
> scan. This is fixed as of 8.0, but all earlier releases are going to be
> slow in that scenario. It's got nothing to do with single vs multi
> column indexes, it is just a shortcoming of the startup code for
> backwards index scans. (I get the impression that the original
> implementation of Postgres' btree indexes only supported unique indexes,
> because there were a number of places where it was horridly inefficient
> for large numbers of equal keys. I think this 8.0 fix is the last such
> issue.)
>
> Since your index has an additional column, there is a hack you can use
> to get decent performance in 7.4 and before. Add a dummy condition on
> the last column:
> where host_luid = 3::bigint
> and log_luid = 2::bigint
> AND LUID <= someverylargevalue::bigint
> and error is null
> order by host_luid desc, log_luid desc, luid desc
> limit 1
> Now, instead of positioning to the first row with value (3,2,anything)
> the initial btree descent will home in on the end of that range, and
> so the expensive stepping over all the rows between is avoided.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>