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

On Wed, Apr 14, 2021 at 5:43 PM James Coleman <jtc331(at)gmail(dot)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?

I think if we can get the correct plan the thing to look at would be
the tlists at the relevant levels of the plan.

--
Robert Haas
EDB: http://www.enterprisedb.com