From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> |
Cc: | David Schmitz <david(dot)schmitz(at)harman(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: performance penalty between Postgresql 8.3.8 and 8.4.1 |
Date: | 2009-12-08 04:04:31 |
Message-ID: | 603c8f070912072004x78950d5p3b7a779c56690e16@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Mon, Dec 7, 2009 at 5:19 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> "Schmitz, David" <david(dot)schmitz(at)harman(dot)com> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Niu Yan | 2009-12-08 05:37:06 | error occured in dbt2 against with postgresql |
Previous Message | Hasini Gunasinghe | 2009-12-08 03:07:55 | Dynamlically updating the estimated cost of a transaction |