On Fri, Aug 19, 2016 at 1:31 PM, Sameer Kumar <sameer.ku...@ashnik.com> wrote:
> > > 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 > > Unless I misunderstand what you mean or how it works I cant really see what it would help. I want my query to still return the "best" results, and I want it to use the index for that. Just randomly selecting out from the whole table will either have to sample a too small subset of the rows, or be too slow. So, given my query above, in the normal ("slow" case) I would find the 10 first even rows: 2,4,6,8,10,12,14,16,18,20 If I could restrict the heap fetches to 10 I would find 2,4,6,8,10 However, with tablesample I might end up with for example these rows: 15024,71914,51682,7110,61802,63390,98278,8022,34256,49220 In my use case I want the best rows (according to the order by), so just a random sample is not good enough. /Victor > >> 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 >
