Re: Postgres seems to use indexes in the wrong order - Mailing list 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.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.
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:Adding some info on the query and table structure (and indexes) would be helpful here.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.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.What are the values for below parameters-- random_page_cost- seq_page_cost- effective_cache_sizeI 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
This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).
Attachment
pgsql-general by date: