Re: 8.2.4 Chooses Bad Query Plan - Mailing list pgsql-performance

From Tom Lane
Subject Re: 8.2.4 Chooses Bad Query Plan
Date
Msg-id 3926.1188335172@sss.pgh.pa.us
Whole thread Raw
In response to Re: 8.2.4 Chooses Bad Query Plan  (Pallav Kalva <pkalva@livedatagroup.com>)
List pgsql-performance
Pallav Kalva <pkalva@livedatagroup.com> writes:
> I have analyzed tables again and also my default_stats_target is set to
> 100, still it shows the same plan.

>>> ->  Index Scan using idx_accountactivity_fkactivityid on
>>> accountactivity accountact0_  (cost=0.00..3.94 rows=1 width=16)
>>> Index Cond: (accountact0_.fkactivityid =
>>> activity1_.activityid)
>>> Filter: (fkaccountid = 1455437)

>>> ->  Index Scan using
>>> idx_accountactivity_fkaccountid on accountactivity accountact0_
>>> (cost=0.00..1641.42 rows=1343 width=16) (actual time=115.348..864.416
>>> rows=10302 loops=1)
>>> Index Cond: (fkaccountid = 1455437)

Oh, my bad, I failed to look closely enough at these subplans.
I thought they were identical but they're not using the same scan
conditions, so the rowcount estimates shouldn't be comparable after all.

Could you try EXPLAINing (maybe even with ANALYZE) the query *without*
the LIMIT clause?  I'm curious to see what it thinks the best plan is
then.

            regards, tom lane

pgsql-performance by date:

Previous
From: Pallav Kalva
Date:
Subject: Re: 8.2.4 Chooses Bad Query Plan
Next
From: Decibel!
Date:
Subject: Re: io storm on checkpoints, postgresql 8.2.4, linux