Help on Index only scan

From: Ertan Küçükoğlu <ertan(dot)kucukoglu(at)1nar(dot)com(dot)tr>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Help on Index only scan
Date: 2017-08-13 21:59:20
Message-ID: 028001d3147f$6b1840b0$4148c210$@1nar.com.tr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Melvin Davidson 2017-08-13 22:15:19 Re: Help on Index only scan
Previous Message George Neuner 2017-08-13 20:27:12 Re: Where is pg_hba.conf