Re: slow query on tables with new columns added.

I have full access to the database, but no access to the application source code.  If creating an index will help, I can do that, but with the columns I don't see it helping as I don't have access to the application source to change that.

So yes, by changing settings, I would like to know if there's any memory settings I can change to help or create an index.  There is an index on the customer_id column in the gltx table, so I'm not sure what else could be done.

If there was a way to create a select trigger, I would do it and return 0 for both columns on that customer_id as it should always be 0.


On 09/24/2011 12:10 AM, Filip Rembiałkowski wrote:

2011/9/23 M. D. <lists@turnkey.bz>

I did a software upgrade, and with it came a new feature where when selecting a customer it queries for the sum of a few columns.  This takes 7 seconds for the 'Cash Sale' customer - by far the most active customer. I'd like to see if it's possible to get it down a bit by changing settings.

To make things clear before we search for a solution. You wrote "by changing settings". Is it the only option? Can't you change the query in software? Can't you change database schema (add indexes etc)?
 

Query:
explain analyse select sum(item_points),sum(disc_points) from invoice left join gltx on invoice.invoice_id = gltx.gltx_id
where gltx.inactive_on is null and gltx.posted = 'Y' and gltx.customer_id = 'A0ZQ2gsACIsEKLI638ikyg'

Aside from other things, you know that LEFT join here is useless? - planner should collapse it to normal join but I'd check.


Filip