Re: "could not find pathkey item to sort" for TPC-DS queries 94-96

On Wed, Apr 14, 2021 at 8:21 PM Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Wed, Apr 14, 2021 at 5:43 PM James Coleman <jtc331@gmail.com> wrote:
> > The query in question is:
> > select  count(*)
> >         from store_sales
> >             ,household_demographics
> >             ,time_dim, store
> >         where ss_sold_time_sk = time_dim.t_time_sk
> >             and ss_hdemo_sk = household_demographics.hd_demo_sk
> >             and ss_store_sk = s_store_sk
> >             and time_dim.t_hour = 15
> >             and time_dim.t_minute >= 30
> >             and household_demographics.hd_dep_count = 7
> >             and store.s_store_name = 'ese'
> >         order by count(*)
> >         limit 100;
> >
> > From debugging output it looks like this is the plan being chosen
> > (cheapest total path):
> >         Gather(store_sales household_demographics time_dim) rows=60626
> > cost=3145.73..699910.15
> >                 HashJoin(store_sales household_demographics time_dim)
> > rows=25261 cost=2145.73..692847.55
> >                   clauses: store_sales.ss_hdemo_sk =
> > household_demographics.hd_demo_sk
> >                         HashJoin(store_sales time_dim) rows=252609
> > cost=1989.73..692028.08
> >                           clauses: store_sales.ss_sold_time_sk =
> > time_dim.t_time_sk
> >                                 SeqScan(store_sales) rows=11998564
> > cost=0.00..658540.64
> >                                 SeqScan(time_dim) rows=1070
> > cost=0.00..1976.35
> >                         SeqScan(household_demographics) rows=720
> > cost=0.00..147.00
>
> This doesn't really make sense to me given the strack trace in the OP.
> That seems to go Limit -> Sort -> Agg -> NestLoop -> NestLoop ->
> NestLoop -> GatherMerge -> Sort. If the plan were as you have it here,
> there would be no Sort and no Gather Merge, so where would be getting
> a failure related to pathkeys?

Also I just realized why this didn't make sense -- I'm not sure what
the above path is. It'd gotten logged as part of the debug options I
have configured, but it must be 1.) incomplete (perhaps at a lower
level of path generation) and/or not the final path selected.

My apologies for the confusion.

James