On Thu, Jan 19, 2017 at 1:28 AM, Tomas Vondra
<tomas.vondra@2ndquadrant.com> wrote:
> On 01/19/2017 06:21 AM, Merlin Moncure wrote:
>>
> ...
>>
>>
>> yes. Either way, I would like to very much understand how server is
>> preferring 3m cost plan to 2.6k cost plan or is otherwise unable to
>> access the cheap plan.
>>
>
> Perhaps it doesn't even see the 2.6k cost - it may not generate the plan for
> some reason, or it arrives to different estimates.
>
> The OP was repeatedly asked for details of the configuration, I've even sent
> a query for doing that a few days back. It's difficult to help when such
> basic info is not provided, though.
>
> It'd also be interesting to see the statistics for the tables involved, so
> something like
>
> SELECT * FROM pg_stats WHERE tablename IN (... tables ...)
>
> and
>
> SELECT relname, relpages, reltuples, relallvisible
> FROM pg_class WHERE relame IN (... tables ...)
>
> might shed some light on what the planner assumes about the data.
>
> Of course, there are other things we might need to know. For example if
> there are foreign keys between the tables, 9.6 is using that for the
> estimates (unlike 9.5). And so on.
Maybe. This smells like bug or missing index. Key for me is:
-> Seq Scan on tblpuorderstatus os (cost=0.00..96501.53
rows=11185842 width=8) (actual time=0.011..822.937 rows=11182962
I can't see why any reasonable plan path would choose this unless the
index on "vip_order_id" is missing (my money is on this actually) or
there is a gross preference to prefer parallel scans over traditional
plan choices irrespective of cost.
I'm rolling out 9.6 next week and so have a particular interest in
this. :-) I tend also utilize a lot of cascading semi-joins and so
am curious to see where this turns up.
Ravi, if you can arrange for screen share or some similar way of
looking at this system I'd be happy to help you trouble shoot, gratis.
merlin