On Mon, Dec 7, 2009 at 5:19 PM, Kevin Grittner <Kevin.Grittner@xxxxxxxxxxxx> wrote: > "Schmitz, David" <david.schmitz@xxxxxxxxxx> wrote: > >> It is carried out with poor performance on postgresql 8.4.1 >> However postgresql 8.3.8 performs just fine. >> If you take a closer look at the query with EXPLAIN, it becomes >> obvious, that postgresql 8.4 does not consider the primary key at >> level 3 and instead generates a hash join: > >> Postgresql 8.4.1: >> >> Sort (cost=129346.71..129498.64 rows=60772 width=61) > >> Postgresql 8.3.8: >> >> Sort (cost=3792.75..3792.95 rows=81 width=61) > > It determines the plan based on available statistics, which in this > case seem to indicate rather different data. Do the two databases > have identical data? Have they both been recently analyzed? What > is the default_statistics_target on each? Do any columns in these > tables have overrides? I think Tom made some changes to the join selectivity code which might be relevant here, though I'm not sure exactly what's going on. Can we see, on the 8.4.1 database: SELECT SUM(1) FROM rdf_admin_hierarchy; SELECT s.stadistinct, s.stanullfrac, s.stawidth, array_upper(s.stanumbers1, 1) FROM pg_statistic s WHERE s.starelid = 'rdf_admin_hierarchy'::regclass AND s.staattnum = (SELECT a.attnum FROM pg_attribute a WHERE a.attname = 'admin_place_id' AND a.attrelid = 'rdf_admin_hierarchy'::regclass); ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
