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