From: Shaun Thomas <sthomas@optionshouse.com>
To: Grzegorz Olszewski <grzegorz.olszewski@outlook.com>
To: AI Rumman <rummandba@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Planner doesn't take indexes into account
Date: Wed, 28 May 2014 08:31:38 -0500
Message-ID: <5385E53A.4000406@optionshouse.com> (raw)
In-Reply-To: <DUB126-W305F8E82EBE7215F89435A91250@phx.gbl>
References: <DUB126-W4724F21FDFBF1316E4FF90913A0@phx.gbl>
<CAGoODpfb_3yWrN5N4P-8fL5Q0Tz0ZOqSL-HiVLrY0FCE=_9b9A@mail.gmail.com>
<DUB126-W305F8E82EBE7215F89435A91250@phx.gbl>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 05/28/2014 04:59 AM, Grzegorz Olszewski wrote:
> There is about 500,000 rows and about 500 new rows each business day.
>
> About 96% of rows meet given conditions, that is, count shoud be about
> 480,000.
Heikki is right on this. Indexes are not a magic secret sauce that are
always used simply because they exist. Think of it like this...
If the table really matches about 480,000 rows, by forcing it to use the
index, it has to perform *at least* 480,000 random seeks. Even if you
have a high-performance SSD array that can do 100,000 random reads per
second, you will need about five seconds just to read the data.
A sequence scan can perform that same operation in a fraction of a
second because it's faster to read the entire table and filter out the
*non* matching rows.
Indexes are really only used, or useful, when the number of matches is
much lower than the row count of the table. I highly recommend reading
up on cardinality and selectivity before creating more indexes. This
page in the documentation does a really good job:
http://www.postgresql.org/docs/9.3/static/row-estimation-examples.html
--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
--
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: sthomas@optionshouse.com, grzegorz.olszewski@outlook.com, rummandba@gmail.com
Subject: Re: Planner doesn't take indexes into account
In-Reply-To: <5385E53A.4000406@optionshouse.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