On Fri, Feb 1, 2013 at 1:50 PM, Richard Huxton <dev@archonet.com> wrote:
1. You said config_id was now "smallint" in your email, but it reads "int" in the pastes above.
Doesn't matter much which, but just checking we've got the right pastes.
You have the correct pastes. I did an alter to int in an attempt to see if that made any difference. It didn't. It takes a couple hours to do that alter so I didn't convert it back to smallint.
2. The total estimated cost of both queries is about the same (477,225.19 for the varchar, 447,623.86 for the int). This suggests something about your configuration doesn't match the performance of your machine, since presumably the int version is taking at least twice as long as the varchar one.
Configuration is pretty standard. As for the machine, it's a VM in an ESXi setup, with dedicated resources. The disk is very fast and is barely touched. One CPU sits at 100% for as long as I let the query run. There is 18GB RAM and PostgreSQL is the only service running on the machine.
3. Interestingly, the config_id search on both plans seems to be using a Bitmap Index, so I'm not sure that's the root cause. However, the varchar version seems to have a literal string it's matching against. If you've manually substituted in a literal value, that could be skewing the tests.
That's why I sent the followup re-parameterizing everything. And the explains are on prepared statements with the parameterization. If I just put the parameter values directly into the query and run it straight, it's fast.
1. Can you just check and see if any of the row estimates are horribly off for any particular clause in the query?
Yes they are. The places where the estimate is rows=1, particularly.
2. You mention your config settings are mostly at default. What's your work_mem and can you increase it? You can issue a SET for the current session, no need to change it globally. If you've got the RAM try doubling it, then double it again. See what happens to your plan then.
21861KB. I tried setting it to 192MB and re-preparing the same statement. Here's the explain execute: http://explain.depesz.com/s/pZ0, which looks identical as before.
