Limit Heap Fetches / Rows Removed by Filter in Index Scans

From: Victor Blomqvist <vb(at)viblo(dot)se>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Limit Heap Fetches / Rows Removed by Filter in Index Scans
Date: 2016-08-19 05:06:34
Message-ID: CAL870DXiKS7Dyj752yK6dG7qwBQaBqqifDzwgiJH5C=mdJCm=A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

Is it possible to break/limit a query so that it returns whatever results
found after having checked X amount of rows in a index scan?

For example:
create table a(id int primary key);
insert into a select * from generate_series(1,100000);

select * from a
where id%2 = 0
order by id limit 10

In this case the query will "visit" 20 rows and filter out 10 of them. We
can see that in the query plan:
"Rows Removed by Filter: 10"
"Heap Fetches: 20"

Is it somehow possible to limit this query so that it only fetches X
amount, in my example if we limited it to 10 Heap Fetches the query could
return the first 5 rows?

My use case is I have a table with 35 million rows with a geo index, and I
want to do a KNN search but also limit the query on some other parameters.
In some cases the other parameters restrict the query so much that Heap
Fetches becomes several 100k or more, and in those cases I would like to
have a limit to my query.

Thanks!
/Victor

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sameer Kumar 2016-08-19 05:31:58 Re: Limit Heap Fetches / Rows Removed by Filter in Index Scans
Previous Message gilad905 2016-08-18 19:08:14 Re: Easiest way to compare the results of two queries row by row and column by column