Re: Optimizing DISTINCT with LIMIT

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:

> Gregory Stark wrote:
> Does that know to stop scanning as soon as it has seen 5 distinct values?

Uhm, hm. Apparently not :(

postgres=# create or replace function v(integer) returns integer as $$begin raise notice 'called %', $1; return $1; end$$ language plpgsql volatile;
CREATE FUNCTION
postgres=# select distinct v(i) from generate_series(1,10) as a(i) limit 3;
NOTICE: 00000: called 1
LOCATION: exec_stmt_raise, pl_exec.c:2542
NOTICE: 00000: called 2
LOCATION: exec_stmt_raise, pl_exec.c:2542
NOTICE: 00000: called 3
LOCATION: exec_stmt_raise, pl_exec.c:2542
NOTICE: 00000: called 4
LOCATION: exec_stmt_raise, pl_exec.c:2542
NOTICE: 00000: called 5
LOCATION: exec_stmt_raise, pl_exec.c:2542
NOTICE: 00000: called 6
LOCATION: exec_stmt_raise, pl_exec.c:2542
NOTICE: 00000: called 7
LOCATION: exec_stmt_raise, pl_exec.c:2542
NOTICE: 00000: called 8
LOCATION: exec_stmt_raise, pl_exec.c:2542
NOTICE: 00000: called 9
LOCATION: exec_stmt_raise, pl_exec.c:2542
NOTICE: 00000: called 10
LOCATION: exec_stmt_raise, pl_exec.c:2542
v
---
5
4
6
(3 rows)

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!