From: | Hustler DBA <hustlerdba(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Number of characters in column preventing index usage |
Date: | 2017-02-17 22:19:00 |
Message-ID: | CAM00CHH3H3SLqx8P68OnfuxoLTnkZP80b3PP+_pvbf3=3-OKYw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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=#
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2017-02-17 22:42:26 | Re: Number of characters in column preventing index usage |
Previous Message | Vucomir Ianculov | 2017-02-17 16:52:18 | Re: pgsql connection timeone |