planner/optimizer question

This executes quickly (as expected):

explain select * from flow_stats where src_addr='1.1.1.1'
order by log_date desc limit 5;
NOTICE: QUERY PLAN:

Limit (cost=1241.77..1241.77 rows=5 width=116)
-> Sort (cost=1241.77..1241.77 rows=307 width=116)
-> Index Scan using flow_stats_ix6 on flow_stats
(cost=0.00..1229.07 rows=307 width=116)

Bue this executes slowly:

explain select * from flow_stats where src_addr='1.1.1.1' order by
log_date desc limit 3;
NOTICE: QUERY PLAN:

Limit (cost=0.00..796.61 rows=3 width=116)
-> Index Scan Backward using flow_stats_ix4 on flow_stats
(cost=0.00..81594.14 rows=307 width=116)

Where

flow_stats_ix4 is (log_date)
flow_stats_ix6 is (src_addr,log_date)

The reason for the slowness is that the given source address does not
exist, and it has to scan through the entire index to determine that the
requested value does not exist (same is true for rare values).

Can the optimizer/planner be told to do an 'Index Scan Backward' on
flow_stats_ix6, or even just an 'Index Scan' & Sort? Or are backward scans
of secondary index segments not implemented?

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/