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

Re: Top-k optimizations?

From: Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com>
To: David Fetter <david(at)fetter(dot)org>
Subject: Re: Top-k optimizations?
Date: 2005-01-14 04:24:31
Message-ID: 41E7497F.3080408@cheapcomplexdevices.com (view raw or flat)
Thread:
Lists: pgsql-hackers
David Fetter wrote:
> Folks,
> 
> As this came up in a work situation, I was wondering a little bit
> about the top-k issue.  Right now, top-k is implemented (most easily,
> I think) via a SELECT with a LIMIT and no OFFSET.  3 questions arise
> from this.

I think the simplest LIMIT query doesn't make it easy to show ties;
but if you don't want the equivalent of MSSQL's "with ties" clause
I think LIMIT works well.


> 1.  Are there currently any optimizations specific to top-k in
> PostgreSQL?  If so, what are they?

Well, when I do queries like
  "select * from customers order by dollarsspent desc limit 3"
it happily uses an index on dollarsspent.


> 3.  What kinds of top-k optimizations might (eventually) be included
> in PostgreSQL?

I think a slightly related topic is whether syntactically it'd be
nice if postgresql had the SQL 2003 optional olap features to
specify ways of doing top-k queries as described here:
http://troels.arvin.dk/db/rdbms/#select-top-n

  SELECT * FROM (
   SELECT
     RANK() OVER (ORDER BY age ASC) AS ranking,
     person_id,  person_name, age
   FROM person
) AS foo
WHERE ranking <= 3

Seems IBM and Oracle support that syntax or something very similar.

Yeah, I know it's probably an orthogonal question to the
optimizations one, but might make porting nicer.

In response to

pgsql-hackers by date

Next:From: Greg StarkDate: 2005-01-14 04:50:01
Subject: Re: Bug? 8.0 does not use partial index
Previous:From: Tom LaneDate: 2005-01-14 04:17:32
Subject: Re: FATAL: catalog is missing 1 attribute(s) for relid 16396

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