On Fri, May 16, 2008 at 12:21 PM, Ben <bench@xxxxxxxxxxxxxxx> wrote: > On Fri, 16 May 2008, Scott Marlowe wrote: > >> Just for giggles, try running the query like so: >> >> set enable_nestloop = off; >> explain analyze ... >> >> and see what happens. I'm guessing that the nested loops are bad choices >> here. > > You guess correctly, sir! Doing so shaves 3 orders of magnitude off the > runtime. That's nice. :) But that brings up the question of why postgres > thinks nested loops are the way to go? It would be handy if I could make it > guess correctly to begin with and didn't have to turn nested loops off each > time I run this. Well, I'm guessing that you aren't in locale=C and that the text functions in your query aren't indexed. Try creating an index on them something like: create index abc_txtfield_func on mytable (substring(textfield,1,5)); etc and see if that helps. As for the char type, I totally understand the issue, having inherited oracle dbs before...
