Re: Postgres seems to use indexes in the wrong order

From: Tim Uckun <timuckun(at)gmail(dot)com>
To: Sameer Kumar <sameer(dot)kumar(at)ashnik(dot)com>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Postgres seems to use indexes in the wrong order
Date: 2015-01-26 22:59:24
Message-ID: CAGuHJrPnxCr-j=7OhpEY7gXrGn05KE1BF9zfaZfDS692L001zA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Sorry I forgot about the table description.

The table is pretty simple There are about 15 fields and about 75 million
records. This query is supposed to use three fields to narrow down the
records. One is a timestamp column, the other is a macaddress type, the
third is a integer. All three are indexed.

The query seems to first use the timestamp column which results in a huge
number of records and then filters out using the integer and the macaddr
indices. If it was to use the integer index first it would start with a
tiny number of records.

On Sun, Jan 25, 2015 at 6:08 AM, Sameer Kumar <sameer(dot)kumar(at)ashnik(dot)com>
wrote:

>
> On Fri, Jan 23, 2015 at 3:04 PM, Tim Uckun <timuckun(at)gmail(dot)com> wrote:
>
>> Take a look at this explain
>>
>> http://explain.depesz.com/s/TTRN
>>
>
> ​Adding some info on the query and table structure (and indexes) would be
> helpful here.​
>
>
>>
>>
>> The final number of records is very small but PG is starting out with a
>> massive number of records and then filtering most of them out.
>>
>> I don't want to really force pg to always use the same index because in
>> some cases this strategy would win but I am wondering if there is anything
>> I need to do in order to get the planner to make better decisions.
>>
>>
> ​What are the values for below parameters-
>
> - random_page_cost
> - seq_page_cost
> ​- effective_cache_size
>
>
>
>> I already did an analyze on the table.
>
>
>
>
> Best Regards,
>
> *Sameer Kumar | Database Consultant*
>
> *ASHNIK PTE. LTD.*
>
> 101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
>
> M: *+65 8110 0350 <%2B65%208110%200350>* T: +65 6438 3504 |
> www.ashnik.com
>
> *[image: icons]*
>
>
>
> [image: Email patch] <http://www.ashnik.com/>
>
>
>
> This email may contain confidential, privileged or copyright material and
> is solely for the use of the intended recipient(s).
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Christophe Pettus 2015-01-26 23:35:39 Re: Logical decoding output plug-in questions
Previous Message Tim Smith 2015-01-26 22:38:30 Versioning Schema SQL ideas needed