Re: Performance issue 6.5 versus 7.0

Herbert Liechti <Herbert.Liechti@thinx.ch> writes:
> I have one problem with a query which is joining 3 tables and which
> is returning one record. In 6.5.3 the results came up immediately.
> In 7.0 the same query takes about 5 seconds. Here the explain
> results  (Same database with the same amount of records and
> same indexes, vacuum was executed before)

Um, did you do a VACUUM ANALYZE, or just a VACUUM?

It looks to me like the major problem here is that 7.0 is estimating
that the query will return a lot of rows, and it's therefore producing
a heavy-duty plan that would do well with a large number of matching
rows.  But I'm guessing that in reality, Person.Person_Id and
Anschrift.Anschrift_Id are unique columns, so the actual number of rows
out should be just the same as the number of MailingListe rows selected
by "MailingListe_Id = 2104", not so?  And that number is probably only
a few dozen?  For a small number of rows, 6.5.3's simple nested-loop-
with-inner-indexscan plan is probably just the right thing.  The trick
is to get the planner to realize that it's only looking for a small
number of rows...

It would help to know how large each of these tables are, and how many
rows you expect to get from the query.  Also, if you have done a VACUUM
ANALYZE, I would like to see the stats produced by the analyze.  You can
get those with a query like

select attname,attdisbursion,s.*
from pg_statistic s, pg_attribute a, pg_class c
where starelid = c.oid and attrelid = c.oid and staattnum = attnum
and relname = 'anschrift';

and similarly for the other two tables.

            regards, tom lane