Yes, both queries are the same, I just shorten the parameter value to see what would have happened. The database that I inherited has a column that stores GUID/UUIDs in a varchar(255) and a select on that table on that column is doing a FULL TABLE SCAN (seq scan). All the values in the column are 36 characters long. The table is 104 KB.
I realize that there was no index on that column so when I created the index and tried to search on a parameter value, it doesn't use the index, but when I shorten the parameter value then the optimizer decides to use an index for the search. On Fri, Feb 17, 2017 at 5:52 PM, Tomas Vondra <tomas.von...@2ndquadrant.com> wrote: > On 02/17/2017 11:42 PM, David G. Johnston wrote: > >> On Fri, Feb 17, 2017 at 3:19 PM, Hustler DBA <hustler...@gmail.com >> <mailto:hustler...@gmail.com>>wrote: >> >> >> >> my_db=# create index tab_idx1 on tab(ID); >> >> CREATE INDEX >> my_db=# explain (analyze, buffers) select count(*) from tab where ID >> = '01625cfa-2bf8-45cf' ; >> QUERY >> PLAN >> ------------------------------------------------------------ >> ------------------------------------------------------------ >> --------------- >> Aggregate (cost=8.29..8.30 rows=1 width=0) (actual >> time=0.048..0.048 rows=1 loops=1) >> Buffers: shared read=2 >> -> Index Only Scan using tab_idx1 on tab (cost=0.27..8.29 >> rows=1 width=0) (actual time=0.043..0.043 rows=0 loops=1) >> Index Cond: (ID = '01625cfa-2bf8-45cf'::text) >> >> >> >> -> Seq Scan on tab (cost=0.00..14.79 rows=5 width=0) (actual >> time=0.031..0.108 rows=5 loops=1) >> Filter: ((ID)::text = >> '01625cfa-2bf8-45cf-bf4c-aa5f3c6fa8ea'::text) >> Rows Removed by Filter: 218 >> Buffers: shared hit=12 >> Planning time: 0.122 ms >> Execution time: 0.180 ms >> (8 rows) >> >> >> IIRC the only reason the first query cares to use the index is because >> it can perform an Index Only Scan and thus avoid touching the heap at >> all. If it cannot avoid touching the heap the planner is going to just >> use a sequential scan to retrieve the records directly from the heap and >> save the index lookup step. >> >> > I don't follow - the queries are exactly the same in both cases, except > the parameter value. So both cases are eligible for index only scan. > > regards > > -- > Tomas Vondra http://www.2ndQuadrant.com > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >
