Re: Need help - optimizer trouble

On Thu, Apr 05, 2001 at 12:01:39AM +0200, Helge Bahmann wrote:
> 7.0.2 will use the following query plan, query takes ~1 second:
>
> Nested Loop (cost=0.00..27.53 rows=2 width=32)
> -> Nested Loop (cost=0.00..10.00 rows=4 width=20)
> -> Index Scan using author_keyword_pkey on author_keyword kw (cost=0.00..2.02 rows=1 width=4)
> -> Index Scan using written_by_idx on ist_autor_von (cost=0.00..7.95 rows=2 width=16)
> -> Index Scan using document_pkey on document (cost=0.00..4.16 rows=1 width=12)
>
> 7.1beta4 uses the following query plan, query takes ~150 seconds:
>
> Nested Loop (cost=8562.09..39846.62 rows=4 width=32)
> -> Index Scan using author_keyword_pkey on author_keyword kw (cost=0.00..2.02 rows=1 width=4)
> -> Materialize (cost=37969.52..37969.52 rows=100000 width=28)
> -> Hash Join (cost=8562.09..37969.52 rows=100000 width=28)
> -> Seq Scan on document (cost=0.00..11932.00 rows=100000 width=12)
> -> Hash (cost=5129.55..5129.55 rows=203555 width=16)
> -> Seq Scan on written_by (cost=0.00..5129.55 rows=303555 width=16)
>
> The two databases are not identical, but they were created using the
> same script which fills the database with random data of the above
> structure. I recreated the databases several times, so it is completely
> repeatable.

If the amount of data in the databases is similar, it looks like the
planner's guesses are totally off. Have you VACUUM ANALYZE'd both databases
recently?

(That's the obvious question. I'll leave it to the PgSQL gurus to help
you if that's not the problem.)

Cheers,

Neil

--
Neil Conway <neilconway(at)home(dot)com>
Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc
Encrypted mail welcomed

Vegetarians do not love animals... they hate plants.