Thread: pkey is not used on productive database
Hi,
I need help to understand the issue on a productive database for a select that takes more time than expected.
1- On a development database I ran the query (select) and I can see on Explain Analyze pgAdmin use all the indexes and primary keys defined. Dev db has almost 10% of productive data.
2- On productive database the same query on Explain Analyze from pgAdmin shows a diferent planning, not using a pkey index and instead uses the progressive scan on a million of rows. I can see Primary key is defined for the table on pgAdmin.
What could be the issue on a productive for not use the pkey index for the SELECT?
Cristian C. Bittel
On Mon, Oct 03, 2011 at 02:48:10PM -0300, Soporte @ TEKSOL S.A. wrote: > Hi, > > > > I need help to understand the issue on a productive database for a select > that takes more time than expected. > > > > 1- On a development database I ran the query (select) and I can see on > Explain Analyze pgAdmin use all the indexes and primary keys defined. Dev db > has almost 10% of productive data. > > 2- On productive database the same query on Explain Analyze from > pgAdmin shows a diferent planning, not using a pkey index and instead uses > the progressive scan on a million of rows. I can see Primary key is defined > for the table on pgAdmin. > > > > What could be the issue on a productive for not use the pkey index for the > SELECT? general answer: http://www.depesz.com/index.php/2010/09/09/why-is-my-index-not-being-used/ but for more details, we'd need to see the query and \d of the table. Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/
On Mon, Oct 3, 2011 at 11:48 AM, Soporte @ TEKSOL S.A. <soporte@teksol.com.ar> wrote: > Hi, > > I need help to understand the issue on a productive database for a select > that takes more time than expected. > 1- On a development database I ran the query (select) and I can see on > Explain Analyze pgAdmin use all the indexes and primary keys defined. Dev db > has almost 10% of productive data. > > 2- On productive database the same query on Explain Analyze from > pgAdmin shows a diferent planning, not using a pkey index and instead uses > the progressive scan on a million of rows. I can see Primary key is defined > for the table on pgAdmin. > > What could be the issue on a productive for not use the pkey index for the > SELECT? Please post the schema, query and explain analyze output of the runs on both machines.