From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: pgsql-performance@postgresql.org
Subject: Re: Number of characters in column preventing index usage
Date: Fri, 17 Feb 2017 23:52:43 +0100
Message-ID: <195020ec-053f-6387-25f8-3f354da6cc38@2ndquadrant.com> (raw)
In-Reply-To: <CAKFQuwZrKf52hNtc124zEVgNgQ7cG18yY2ynJJPLhTHpzarihg@mail.gmail.com>
References: <CAM00CHH3H3SLqx8P68OnfuxoLTnkZP80b3PP+_pvbf3=3-OKYw@mail.gmail.com>
<CAKFQuwZrKf52hNtc124zEVgNgQ7cG18yY2ynJJPLhTHpzarihg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tomas.vondra@2ndquadrant.com
Subject: Re: Number of characters in column preventing index usage
In-Reply-To: <195020ec-053f-6387-25f8-3f354da6cc38@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox