> It will probably be useful with EXPLAIN ANALYZE of your
> queries, not just the EXPLAIN.
it took 245 seconds to complete, see below.
> It looks like the planner thinks this is going to be really
> cheap -- so it's misestimating something somewhere. Have you
> ANALYZEd recently?
yes, but to
I recently tried to upgrade to 8.2.4, but major queries I wrote for 8.1.4 are
now planned differently on 8.2.4 and are no longer usable. What the 8.1.4
planned as a series of 'hash left join's and took about 2 seconds now is
planned as 'nested loop left joins' and takes forever.
Other request w
> Is this with the join collapse limit set to 1, or with
> default? (Default is generally more interesting.)
below is the same query with the default setting.
regards,
Liviu
"Nested Loop Left Join (cost=23.35..1965.46 rows=1 width=125) (actual
time=50.408..231926.123 rows=2026 loops=1)"
"
> This seems to be the source of the misestimation. You might
> want to try using "n WHERE n.nodein NOT IN (SELECT nodeid
> FROM templates)" instead of "n LEFT JOIN templates USING
> (nodeid) WHERE templates.nodeid IS NULL" and see if it helps.
it helped, the new version of the query takes 2303
> > it helped, the new version of the query takes 2303 ms on both 8.1.4
> > and 8.2.4.
>
> And the old one?
slightly shorter, 2204 ms.
as a subjective perception, the entire application is slightly slower on 8.2.4,
probably there are many queries that were manually tunned for 7.x/8.1.x and now
> under some alignments of the planets 8.1 has similar problems.
8.1 might have similar problems, but the point here is different: if what
was manually tuned to work in 8.1 confuses the 8.2 planner and performance
drops so much (from 2303 to 231929 ms in my case) upgrading a production
machine to
> It is arguable, that updating the DB software version in an
> enterprise environment requires exactly that: check all
> production queries on the new software to identify any
> issues. In part, this is brought on by the very tuning that
> you performed against the previous software. Restore t
