Number of characters in column preventing index usage

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I am seeing this strange behavior, I don't know if this is by design by Postgres. 

I have an index on a column which is defined as "character varying(255)". When the value I am searching for is of a certain length, the optimizer uses the index but when the value is long, the optimizer doesn't use the index but does a seq scan on the table. Is this by design? How can I make the optimizer use the index no matter what the size/length of the value being searched for?   


PostgreSQL version: 9.4 


my_db=# explain (analyze, buffers) select count(*) from tab where ID = '01625cfa-2bf8-45cf-bf4c-aa5f3c6fa8ea' ;
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=14.80..14.81 rows=1 width=0) (actual time=0.114..0.114 rows=1 loops=1)
   Buffers: shared hit=12
   ->  Seq Scan on tab  (cost=0.00..14.79 rows=5 width=0) (actual time=0.025..0.109 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.155 ms
 Execution time: 0.167 ms
(8 rows)

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)
         Heap Fetches: 0
         Buffers: shared read=2
 Planning time: 0.250 ms
 Execution time: 0.096 ms
(8 rows)

my_db=# explain (analyze, buffers) select count(*) from tab where ID = '01625cfa-2bf8-45cf-bf4c-aa5f3c6fa8ea' ;
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=14.80..14.81 rows=1 width=0) (actual time=0.115..0.115 rows=1 loops=1)
   Buffers: shared hit=12
   ->  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)

my_db=# 




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux