On Sun, Aug 13, 2017 at 5:59 PM, Ertan Küçükoğlu < ertan.kucuko...@1nar.com.tr> wrote:
> Hello, > > My table details: > robox=# \dS+ updates > Table "public.updates" > Column | Type | Modifiers > | Storage | Stats target | Description > ---------------+---------+---------------------------------- > ---------------- > ---------+----------+--------------+------------- > autoinc | integer | not null default > nextval('updates_autoinc_seq'::regclass) | plain | | > filename | text | > | extended | | > dateofrelease | date | > | plain | | > fileversion | text | > | extended | | > afile | text | > | extended | | > filehash | text | > | extended | | > active | boolean | > | plain | | > Indexes: > "updates_pkey" PRIMARY KEY, btree (autoinc) > "update_filename" btree (filename) > "updates_autoinc" btree (autoinc DESC) > "updates_dateofrelease" btree (dateofrelease) > "updates_filename_dateofrelease" btree (filename, dateofrelease) > > > robox=# select count(autoinc) from updates; > count > ------- > 2003 > (1 row) > > robox=# select autoinc, filename, fileversion from updates limit 10; > autoinc | filename | fileversion > ---------+----------------------------------+------------- > 18 | Robox.exe | 1.0.1.218 > 19 | Robox.exe | 1.0.1.220 > 20 | Robox.exe | 1.0.1.220 > 21 | 8423bfc5a669864f9b66b6b15ce908b9 | 1.1.1.1 > 22 | 4fdabb0c7adbc5a89fbe679ce76ccef9 | 1.1.1.1 > 23 | f469d77bfa86c8917c7846c0f871137c | 1.1.1.1 > 24 | bc10af4c8789718a9ca6565ea14cb17d | 1.1.1.1 > 25 | d9f87ee46cdb41cd15c2f71ed599faf9 | 1.1.1.1 > 26 | 6f7428a5364aae1d5914a66cba3e6f3b | 1.1.1.1 > 27 | 66ec4cdb8d64ca1414f75c1fb9eaa518 | 1.1.1.1 > (10 rows) > > 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; > > I simply could not understand planner and cannot provide right index for > it. > Below index names "update_filename" and "updates_autoinc" are added just > for > the query that I would like to have a index only scan plan. I also failed > with following indexes > "autoinc desc, filename, fileversion" > "autoinc desc, filename" > > First 3 rows in above select results are actual data. You will find that I > have inserted about 2000 rows of dummy data to have somewhat meaningful > plan > for the query. > > Current planner result: > robox=# vacuum full; > VACUUM > robox=# explain analyze > robox-# select autoinc, fileversion > robox-# from updates > robox-# where filename = 'Robox.exe' > robox-# order by autoinc desc; > QUERY PLAN > ------------------------------------------------------------ > ---------------- > -------------------------------------------------- > Sort (cost=12.79..12.79 rows=3 width=12) (actual time=0.047..0.047 rows=3 > loops=1) > Sort Key: autoinc DESC > Sort Method: quicksort Memory: 25kB > -> Bitmap Heap Scan on updates (cost=4.30..12.76 rows=3 width=12) > (actual time=0.040..0.040 rows=3 loops=1) > Recheck Cond: (filename = 'Robox.exe'::text) > Heap Blocks: exact=1 > -> Bitmap Index Scan on update_filename (cost=0.00..4.30 rows=3 > width=0) (actual time=0.035..0.035 rows=3 loops=1) > Index Cond: (filename = 'Robox.exe'::text) > Planning time: 1.873 ms > Execution time: 0.076 ms > (10 rows) > > > I appreciate any help on having right index(es) as I simply failed myself. > > Regards, > Ertan Küçükoğlu > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > *First, you do not need index "updates_autoinc", since autoinc is the Primary Key, you are just duplicating the index.* *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. If you really want to force an index scan, then you would have to do* *SET enable_seqscan = off; Before doing the query, however you are just shooting yourself in the foot by doing that* *as it will make the query slower.* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.