
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   |/

Mon, 29 Oct 2001 16:12:23 +1100
Philip Warner <pjw@rhyme.com.au>
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

Mon, 29 Oct 2001 15:40:00 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: planner/optimizer question
At 15:40 29/10/01 +0900, Hiroshi Inoue wrote:
>Philip Warner wrote:
>
>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;
>

Yep, that works. Thanks. 



----------------------------------------------------------------
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   |/

Mon, 29 Oct 2001 20:23:39 +1100
Philip Warner <pjw@rhyme.com.au>
Re: planner/optimizer question