2011/9/26 M. D.
<lists@turnkey.bz> 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.
Hi
I didn't respond earlier, because I actually don't see any easy way of speeding up the query.
The memory settings seem fine for this size of data.
It does not look like you can change things by simply adding indexes. I mean, you can certainly add a specially crafted partial index on gltx.customer_id WHERE (gltx.inactive_on IS NULL) AND (gltx.posted = 'Y') - this can earn you a few percent max.
The problem here might be the type of join columns - we can see they are about 10 characters which is not an ideal choice (that's one of reasons why I'm a fan of artificial integer pkeys).
You _could_ try running the query with enable_mergejoin = off and see what happens.
You can check if the problem persists after dumping and reloading to another db.
If app modification was possible, you could materialize the data _before_ it must be queried - using summary table and appropriate triggers for keeping it up-to-date.
Regarding your last comment - on that customer_id values should be 0 - if it's a persistent business rule, I would try to create a CHECK to reflect it. With some luck and fiddling, constraint_exclusion might come to help with speeding up your query.
Also, if there is something special about customer_id distribution - table partitioning might be an option.
Ok, that's a long list - hope this helps, and good luck.
After all you can throw more hardware at the problem - or hire some Pg magician :-)