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

Re: planner/optimizer question

From: Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp>
To: Philip Warner <pjw(at)rhyme(dot)com(dot)au>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: planner/optimizer question
Date: 2001-10-29 06:40:00
Message-ID: 3BDCF9C0.90E697EA@tpf.co.jp (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Tatsuo IshiiDate: 2001-10-29 06:40:33
Subject: psql bug in 7.2 beta1
Previous:From: Ashley CambrellDate: 2001-10-29 06:35:35
Subject: Best way for Postrgesql to pass info to java and back again? (PL/Java)

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