=?utf-8?Q?Ertan_K=C3=BC=C3=A7=C3=BCko=C4=9Flu?= <ertan.kucukoglu@xxxxxxxxxxx> writes: >>> I want to have an index only scan for my below query: >>> select autoinc, fileversion from updates where filename = 'Robox.exe' order >>> by autoinc desc; >> On 14 Aug 2017, at 01:15, Melvin Davidson <melvin6925@xxxxxxxxx> wrote: >> As far as "Index only scan" , since the table only has 2003 rows, the optimizer has determined it is faster just to >> load all the rows into memory and then filter. > Sorry, my question was misleading. I do not want to use "set enable_seqscan = off" I want to be sure that when necessary (record count increases) relevant index(es) will be used. There's a considerable distance between "is the planner making appropriate use of indexes" and "I insist on an index-only scan". The reason you're not getting an index-only scan here is that that requires an index that includes every column referenced in the query, which you don't have. At minimum you'd need an index including all of autoinc, fileversion, and filename to do this query with an IOS. If you want it to be particularly efficient for this query then you'd need the index's column order to be (filename, autoinc, fileversion) --- putting filename means the entries satisfying WHERE will be clumped in the index, and putting autoinc second means that a backwards scan on that portion of the index is enough to produce the requested sort ordering without an explicit sort step. Whether it's worth maintaining an index this specialized depends on how much update traffic you have versus how often you want to do this particular query. Often it's not worth the extra disk space and update overhead to have such an index. In any case, I wouldn't worry about it until you have an actual performance problem. Trying to tell on toy data what the planner will do with production-sized data is usually a losing game. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
