From: Grzegorz Olszewski <grzegorz.olszewski@outlook.com>
To: Shaun Thomas <sthomas@optionshouse.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 23:03:22 +0200
Message-ID: <DUB126-W644BD47B8A289277CCC38C91250@phx.gbl> (raw)
In-Reply-To: <5385E53A.4000406@optionshouse.com>
References: <DUB126-W4724F21FDFBF1316E4FF90913A0@phx.gbl>
<CAGoODpfb_3yWrN5N4P-8fL5Q0Tz0ZOqSL-HiVLrY0FCE=_9b9A@mail.gmail.com>
<DUB126-W305F8E82EBE7215F89435A91250@phx.gbl>
<5385E53A.4000406@optionshouse.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
OK, thank you very much. I've tried similar query but with very few rows matching. In this case index was present in the plan.
BR,
Grzegorz Olszewski
> Date: Wed, 28 May 2014 08:31:38 -0500
> From: sthomas@optionshouse.com
> To: grzegorz.olszewski@outlook.com; rummandba@gmail.com
> CC: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Planner doesn't take indexes into account
>
> 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: grzegorz.olszewski@outlook.com, sthomas@optionshouse.com, rummandba@gmail.com
Subject: Re: Planner doesn't take indexes into account
In-Reply-To: <DUB126-W644BD47B8A289277CCC38C91250@phx.gbl>
* 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