> Could you show us the result of SELECT version(); ?
of course I can
PostgreSQL 8.3.7 on x86_64-unknown-linux-gnu, compiled by GCC gcc (SUSE
Linux) 4.3.2 [gcc-4_3-branch revision 141291]
>
> Have you done any VACUUM VERBOSE lately and captured the output? If
> so, what do the last few lines say? (That's a lot of relations for
> the number of pages; just curious how it maps to actual.)
It need a lot of time (20 GB database), when I will have the answare
I'll post it
>
> > enable_hashjoin = off
> > enable_nestloop = off
> > enable_seqscan = off
> > enable_sort = off
>
> That's probably a bad idea. If particular queries aren't performing
> well, you can always set these temporarily on a particular connection.
> Even then, turning these off is rarely a good idea except for
> diagnostic purposes. I *strongly* recommend you put all of these back
> to the defaults of 'on' and start from there, turning off selected
> items as needed to get EXPLAIN ANALYZE output to demonstrate the
> better plans you've found for particular queries.
OK, it will became the viceversa of what I'm doing now (set them to on
and set them to off only on the appropriate connection instead of set
them to off and set them to on only on some appropriate connection).
But the question is: do you thing it is impossible to find a
configuration that works fine for both the kind of query? The
application have to run even versus oracle db... i wont have to write a
different source for the two database...
>
> > effective_cache_size = 3600MB
>
> That seems a little on the low side for an 8GB machine, unless you
> have other things on there using a lot of RAM. Do you?
yes there are two instances of postgress running on the same server (the
database have to stay complitely separated).
>
> If you could set the optimizer options back on and get new plans where
> you show specifically which options (if any) where turned off for the
> run, that would be good. Also, please attach the plans to the email
> instead of pasting -- the word wrap makes them hard to read. Finally,
> if you could do \d on the tables involved in the query, it would help.
> I'll hold off looking at these in hopes that you can do the above.
>
> -Kevin
I attach the explanation of the log query after setting all the enable
to on. In this condition the query will never finish...
