Postgres Pro
Downloads
Home   >   mailing lists

Re: [PERFORM] Number of characters in column preventing index usage - Mailing list pgsql-performance

From Hustler DBA
Subject Re: [PERFORM] Number of characters in column preventing index usage
Date
Msg-id CAM00CHFZX=2MSc1g+7LE6XOqLDJmrY5wjkWC3CySVFdNgCD3ng@mail.gmail.com
Whole thread Raw
In response to Re: [PERFORM] Number of characters in column preventing index usage  (Tomas Vondra <tomas.vondra@2ndquadrant.com>)
List pgsql-performance
Tree view
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.vondra@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 <hustlerdba@gmail.com
 <mailto:hustlerdba@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

pgsql-performance by date:

Previous
From: Tomas Vondra
Date:
Subject: Re: [PERFORM] Number of characters in column preventing index usage
Next
From: "David G. Johnston"
Date:
Subject: Re: [PERFORM] Number of characters in column preventing index usage