Re: slow query on tables with new columns added.

2011/9/23 M. D. <lists(at)turnkey(dot)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