On Fri, 19 Aug 2016, 1:07 p.m. Victor Blomqvist, <v...@viblo.se> wrote:
> 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. > Have you checked the TABLESAMPLE clause in v9.5? https://wiki.postgresql.org/wiki/TABLESAMPLE_Implementation > Thanks! > /Victor > -- -- Best Regards Sameer Kumar | DB Solution Architect *ASHNIK PTE. LTD.* 101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533 T: +65 6438 3504 | M: +65 8110 0350 Skype: sameer.ashnik | www.ashnik.com
