Message-ID: <5385E53A.4000406@optionshouse.com>
Date: Wed, 28 May 2014 08:31:38 -0500
From: Shaun Thomas <sthomas@optionshouse.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.5.0
MIME-Version: 1.0
To: Grzegorz Olszewski <grzegorz.olszewski@outlook.com>, AI Rumman
 <rummandba@gmail.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Planner doesn't take indexes into account
References: <DUB126-W4724F21FDFBF1316E4FF90913A0@phx.gbl>,
 <CAGoODpfb_3yWrN5N4P-8fL5Q0Tz0ZOqSL-HiVLrY0FCE=_9b9A@mail.gmail.com>
 <DUB126-W305F8E82EBE7215F89435A91250@phx.gbl>
In-Reply-To: <DUB126-W305F8E82EBE7215F89435A91250@phx.gbl>
Content-Type: text/plain; charset="ISO-8859-2"; format=flowed
content-transfer-encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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