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

Re: Inoptimal query plan for max() and multicolumn index

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Vladimir Kulev" <me(at)lightoze(dot)net>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Inoptimal query plan for max() and multicolumn index
Date: 2011-06-20 15:41:16
Message-ID: 4DFF23CC020000250003E994@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-performance
Vladimir Kulev <me(at)lightoze(dot)net> wrote:
 
> # explain analyze select max(timestamp) from sms where number in
> ('5502712','5802693','5801981');
 
> According to planner cost estimations - it has enough data to
> understand that it is better to aggregate maximum from three
> subqueries. I suppose it's not a bug but not implemented feature
 
Yeah, you're hoping for an optimization which hasn't been
implemented.
 
I expect you're hoping for a plan similar to what this gives you?:
 
explain analyze select greatest(
  (select max(timestamp) from sms where number = '5502712'),
  (select max(timestamp) from sms where number = '5802693'),
  (select max(timestamp) from sms where number = '5801981'));
 
-Kevin

In response to

Responses

pgsql-performance by date

Next:From: Jon NelsonDate: 2011-06-20 15:53:52
Subject: bad plan: 8.4.8, hashagg, work_mem=1MB.
Previous:From: Sushant SinhaDate: 2011-06-20 15:38:59
Subject: sequential scan unduly favored over text search gin index

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