Re: ADD FOREIGN KEY (was Re: [GENERAL] 7.4Beta)

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
>> Hm. Don't suppose you were using EXPLAIN ANALYZE so we could see what's
>> happening? This is clearly a planner failure, although I'm unsure if we
>> can expect the planner to get the right answer with no pg_statistic entries.

> The left join one seems to give me values like the following:

There are some fishy row estimates in here:

> -> Index Scan using pktest_a_key on pktest (cost=0.00..52.00
> rows=1000 width=8) (actual time=17.82..1609.97 rows=10000 loops=1)

The system definitely should be expected to have the accurate row count
for the PK table, since an index should have been created on it (and we
do do that after loading the data, no?). It is possible that it'd have
the default 1000 estimate for the FK table, if there are no indexes at
all on the FK table; otherwise it should have the right number. It's
not real clear to me what conditions you're testing under, but the
estimates in the plans you're quoting aren't consistent ...

regards, tom lane