On Tue, 14 May 2024 at 01:52, Dimitrios Apostolou <jimis@xxxxxxx> wrote: > > On Tue, 14 May 2024, David Rowley wrote: > > The query does contain an ORDER BY, so if the index is not chosen to > > provide pre-sorted input, then something has to put the results in the > > correct order before the LIMIT is applied. > > The last query I tried was: > > SELECT DISTINCT workitem_n FROM test_runs_raw LIMIT 10; I was looking at the original query. In that case, we have 2 ways to remove duplicate rows with DISTINCT, "Hash Aggregate" and "Sort" -> "Unique". Both of these will consume all of their input rows before outputting any rows. DISTINCT with LIMIT is a special case that we don't have a good operator for. In theory, we could have some "Hash Distinct" node type that was less eager to consume all of its input rows. When invoked "Hash Distinct" could consume input rows until it found one that didn't exist in the hash table. I've no idea how that would work when we exceed work_mem. However, most queries with a LIMIT will have an ORDER BY, so such a node likely wouldn't get much use. David
