Thread: Index question on postgres
If you do a:
VACUUM FULL ANALYZE
for both production and for test and then do an EXPLAIN on your queries, what do the plans look like?
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of akp geek
Sent: Thursday, January 07, 2010 8:11 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Index question on postgres
Hi All -
I have query in production and test. The tables in both the environment has the same structure ,indexes and constraints. But the in the test and the prod the explain plan is totally different. In test environment the query is taking long time and noticed that indexes are not being utilized ? I am not able to figure it Can you please share your thoughts?
Regards
On Thu, Jan 7, 2010 at 9:11 PM, akp geek <akpgeek@gmail.com> wrote: > Hi All - > I have query in production and test. The tables in both the > environment has the same structure ,indexes and constraints. But the in the > test and the prod the explain plan is totally different. In test environment > the query is taking long time and noticed that indexes are not being > utilized ? I am not able to figure it Can you please share your thoughts? Assuming you've got the same data in both servers, or data that's at least similar enough to get the same query plan then it's likely that the test db needs analyzing or vacuuming. If you run the same query on each machine with explain analyze, what do you get? i.e.: explain analyze select ... (rest of your query here)
If you do a:
VACUUM FULL ANALYZE
for both production and for test and then do an EXPLAIN on your queries, what do the plans look like?
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of akp geek
Sent: Thursday, January 07, 2010 8:11 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Index question on postgres
Hi All -
I have query in production and test. The tables in both the environment has the same structure ,indexes and constraints. But the in the test and the prod the explain plan is totally different. In test environment the query is taking long time and noticed that indexes are not being utilized ? I am not able to figure it Can you please share your thoughts?
Regards
Post the results here
From: akp geek [mailto:akpgeek@gmail.com]
Sent: Thursday, January 07, 2010 8:30 PM
To: Dann Corbit
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Index question on postgres
I did that and the Explain look different
Regards
On Thu, Jan 7, 2010 at 11:13 PM, Dann Corbit <DCorbit@connx.com> wrote:
If you do a:
VACUUM FULL ANALYZE
for both production and for test and then do an EXPLAIN on your queries, what do the plans look like?
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of akp geek
Sent: Thursday, January 07, 2010 8:11 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Index question on postgres
Hi All -
I have query in production and test. The tables in both the environment has the same structure ,indexes and constraints. But the in the test and the prod the explain plan is totally different. In test environment the query is taking long time and noticed that indexes are not being utilized ? I am not able to figure it Can you please share your thoughts?
Regards
Post the results here
From: akp geek [mailto:akpgeek@gmail.com]
Sent: Thursday, January 07, 2010 8:30 PM
To: Dann Corbit
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Index question on postgres
I did that and the Explain look different
Regards
On Thu, Jan 7, 2010 at 11:13 PM, Dann Corbit <DCorbit@connx.com> wrote:
If you do a:
VACUUM FULL ANALYZE
for both production and for test and then do an EXPLAIN on your queries, what do the plans look like?
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of akp geek
Sent: Thursday, January 07, 2010 8:11 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Index question on postgres
Hi All -
I have query in production and test. The tables in both the environment has the same structure ,indexes and constraints. But the in the test and the prod the explain plan is totally different. In test environment the query is taking long time and noticed that indexes are not being utilized ? I am not able to figure it Can you please share your thoughts?
Regards
On Thu, Jan 7, 2010 at 10:04 PM, akp geek <akpgeek@gmail.com> wrote: > the explain from both enviroments ??? need to be posted. > just one quick question. Why would the index I have created not being used? > Regards Better explain analyze than plain eplain. If the retrieval of data by seq scan is deemed "cheaper" than index acces by postgresql's rather impressive planner system then a seq scan is chosen. There are many methods for joining and so on, so picking the right one can give you much better speed than a simple nested loop only planner etc. You might want to attach the query plans as a text file to preserve format.
On Thu, Jan 7, 2010 at 11:11 PM, akp geek <akpgeek@gmail.com> wrote: > I have query in production and test. The tables in both the environment has > the same structure ,indexes and constraints. But the in the test and the > prod the explain plan is totally different. In test environment the query is > taking long time and noticed that indexes are not being utilized ? I am not > able to figure it Can you please share your thoughts? Is the same amount of data in both? Are the other configs of the server the same?
Is the same amount of data in both? Are the other configs of theOn Thu, Jan 7, 2010 at 11:11 PM, akp geek <akpgeek@gmail.com> wrote:
> I have query in production and test. The tables in both the environment has
> the same structure ,indexes and constraints. But the in the test and the
> prod the explain plan is totally different. In test environment the query is
> taking long time and noticed that indexes are not being utilized ? I am not
> able to figure it Can you please share your thoughts?
server the same?
there ya go. the query plan will change based on the data statistics on the tables and indexes. On Fri, Jan 8, 2010 at 2:09 PM, akp geek <akpgeek@gmail.com> wrote: > The volume of data is less in Test compared to prod. and I synced the > postgresql.conf file in both environments
there ya go. the query plan will change based on the data statistics
on the tables and indexes.
On Fri, Jan 8, 2010 at 2:09 PM, akp geek <akpgeek@gmail.com> wrote:
> The volume of data is less in Test compared to prod. and I synced the
> postgresql.conf file in both environments
From: akp geek [mailto:akpgeek@gmail.com]
Sent: Thursday, January 07, 2010 9:04 PM
To: Dann Corbit
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Index question on postgres
{snip}
Why would the index I have created not being used?
>>
The index you have created will not be used in several circumstances. For instance:
1. It is faster to do a table scan than to use the index, despite up to date statistics
2. The distribution of the data has changed since the last time you analyzed the database
Consider a truly horrible case, an index on a single character. As it turns out, this field contains exactly two values: ‘M’ or ‘F’ for male or female. About 50% of the data is ‘M’ and about 50% is ‘F’. If we were to use this index to scan the data, we will be loading the index pages, and then popping all over the data pages following the index. It will truly be an awful sight. We would spend far more effort than simply doing a table scan. Fortunately, we have statistics which have come to our rescue. They will tell the optimizer to simply ignore the horribly defined index file and never use it in any circumstance.
Consider an even more horrible case, the same index, but we have not updated statistics in months and we have automatic stats and vacuum disabled. The only time statistics was run, there was a single ‘F’ in the index and 44 ‘M’ values. A query comes along looking for “sex = ‘F’” and the optimizer decides to use the index. We can’t blame the poor optimizer. It’s not his fault that statistical collection was disabled. So he merrily informs the query planner to follow the index to collect the data, and the query takes eons to complete.
In short, using the index is not always a good idea. It’s a good idea to use an index when it is faster than not using an index. If you were to post the explain analyze output, experts here could tell you exactly why the decisions were made to use an index or not to use an index. And if an index should have been used, they can tell you what to do so that the index will be used next time.
<<