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

Re: planner/optimizer question

From: "Zeugswetter Andreas SB SD" <ZeugswetterA(at)spardat(dot)at>
To: "Philip Warner" <pjw(at)rhyme(dot)com(dot)au>, <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: planner/optimizer question
Date: 2001-10-29 10:02:01
Message-ID: 46C15C39FEB2C44BA555E356FBCD6FA42127A2@m0114.s-mxs.net (view raw or flat)
Thread:
Lists: pgsql-hackers
> 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

pgsql-hackers by date

Next:From: Tille, AndreasDate: 2001-10-29 12:43:37
Subject: Serious performance problem
Previous:From: Philip WarnerDate: 2001-10-29 09:23:39
Subject: Re: planner/optimizer question

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