Postgres Pro
Downloads
Home   >   mailing lists

Thread: Postgres seems to use indexes in the wrong order

Postgres seems to use indexes in the wrong order

From
Tim Uckun
Date:
Take a look at this explain

http://explain.depesz.com/s/TTRN

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.

I already did an analyze on the table.

Re: Postgres seems to use indexes in the wrong order

From
Adrian Klaver
Date:
On 01/22/2015 11:04 PM, Tim Uckun wrote:
> Take a look at this explain
>
> http://explain.depesz.com/s/TTRN

I maybe missing it, but I do not see the actual query.
>
> 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.
>
> I already did an analyze on the table.


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Postgres seems to use indexes in the wrong order

From
Sameer Kumar
Date:

On Fri, Jan 23, 2015 at 3:04 PM, Tim Uckun <timuckun@gmail.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  T: +65 6438 3504 | www.ashnik.com

icons

 

Email patch

 

This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).

Attachment

Re: Postgres seems to use indexes in the wrong order

From
Tim Uckun
Date:
The effective_cache_size is one gig. The others are not set so therefore the default.



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

On Fri, Jan 23, 2015 at 3:04 PM, Tim Uckun <timuckun@gmail.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  T: +65 6438 3504 | www.ashnik.com

icons

 

Email patch

 

This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).


Attachment

Re: Postgres seems to use indexes in the wrong order

From
Tim Uckun
Date:
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.kumar@ashnik.com> wrote:

On Fri, Jan 23, 2015 at 3:04 PM, Tim Uckun <timuckun@gmail.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  T: +65 6438 3504 | www.ashnik.com

icons

 

Email patch

 

This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).


Attachment

Re: Postgres seems to use indexes in the wrong order

From
Sameer Kumar
Date:

On Tue, Jan 27, 2015 at 6:59 AM, Tim Uckun <timuckun@gmail.com> wrote:
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.

​May be the record distribution of quantiles is skewed.​ Have you tried to set more granular statistics for your int column?

The effective_cache_size is one gig. The others are not set so therefore the default.

​Ideally the effective_cache_size can be set to as much as 50-60% of your available memory. Also you need to tune your random_page_cost as per the behavior of your disk. 

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server​

​If these two does not work then may be you should go for setting a more granular statistics collection for your specific column-

alter table <table_name> alter column <column_Name> set  statistics 1000;
analyze <table_name>;

​


Best Regards,

Sameer Kumar | Database Consultant

ASHNIK PTE. LTD.

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533

M: +65 8110 0350  T: +65 6438 3504 | www.ashnik.com

icons

 

Email patch

 

This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).

Attachment

Re: Postgres seems to use indexes in the wrong order

From
Tim Uckun
Date:
Thanks for the tips. I'll make some adjustments

On Tue, Jan 27, 2015 at 8:38 PM, Sameer Kumar <sameer.kumar@ashnik.com> wrote:

On Tue, Jan 27, 2015 at 6:59 AM, Tim Uckun <timuckun@gmail.com> wrote:
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.

​May be the record distribution of quantiles is skewed.​ Have you tried to set more granular statistics for your int column?

The effective_cache_size is one gig. The others are not set so therefore the default.

​Ideally the effective_cache_size can be set to as much as 50-60% of your available memory. Also you need to tune your random_page_cost as per the behavior of your disk. 

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server​

​If these two does not work then may be you should go for setting a more granular statistics collection for your specific column-

alter table <table_name> alter column <column_Name> set  statistics 1000;
analyze <table_name>;

​


Best Regards,

Sameer Kumar | Database Consultant

ASHNIK PTE. LTD.

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533

M: +65 8110 0350  T: +65 6438 3504 | www.ashnik.com

icons

 

Email patch

 

This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).


Attachment