Re: Optimizing DISTINCT with LIMIT

Heikki Linnakangas <heikki.linnakangas@enterprisedb.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:2542v 
---546
(3 rows)

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