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@xxxxxxxxxxxx> 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 > ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
