David Fetter wrote:
> 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:
SELECT * FROM (
RANK() OVER (ORDER BY age ASC) AS ranking,
person_id, person_name, age
) 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 Stark||Date: 2005-01-14 04:50:01|
|Subject: Re: Bug? 8.0 does not use partial index|
|Previous:||From: Tom Lane||Date: 2005-01-14 04:17:32|
|Subject: Re: FATAL: catalog is missing 1 attribute(s) for relid 16396 |