Skip site navigation (1) Skip section navigation (2)

planner/optimizer question

From: Philip Warner <pjw(at)rhyme(dot)com(dot)au>
To: pgsql-hackers(at)postgresql(dot)org
Subject: planner/optimizer question
Date: 2001-10-29 05:12:23
Message-ID: 3.0.5.32.20011029161223.029ec100@mail.rhyme.com.au (view raw or flat)
Thread:
Lists: pgsql-hackers
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   |/

Responses

pgsql-hackers by date

Next:From: Lincoln YeohDate: 2001-10-29 05:36:58
Subject: Re: Ultimate DB Server
Previous:From: Philip WarnerDate: 2001-10-29 03:13:02
Subject: Odd error in complex query (7.2): Sub-SELECT uses un-GROUPed...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group