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

Search Discussions

  • Hiroshi Inoue at Oct 29, 2001 at 6:37 am

    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
  • Philip Warner at Oct 29, 2001 at 9:24 am

    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 |/
  • Andreas Zeugswetter at Oct 29, 2001 at 10:02 am

    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)
    This would be a possible optimization, that other db's also seem to miss
    (at least in older versions). The trick with all ot them is to include
    the =constant restricted column in the order by:

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

    Note, that because src_addr is fixed it won't change the result.

    Andreas

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedOct 29, '01 at 5:12a
activeOct 29, '01 at 10:02a
posts4
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase