Re: Planner doesn't take indexes into account

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(at)optionshouse(dot)com
> To: grzegorz(dot)olszewski(at)outlook(dot)com; rummandba(at)gmail(dot)com
> CC: pgsql-performance(at)postgresql(dot)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(at)optionshouse(dot)com
>
> ______________________________________________
>
> See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance