Re: Multi-column indexes - Mailing list pgsql-general

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@eSentire.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.944rows=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
>