Re: planner/optimizer question

Philip Warner wrote:
>
> 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?

How about the following ?

explain select * from flow_stats where src_addr='1.1.1.1'
order by src_addr desc, log_date desc limit 3;

regards,
Hiroshi Inoue