Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1

"Mark Kirkwood" <markir(at)paradise(dot)net(dot)nz> writes:

> Here is a (somewhat hurried) self-contained version of the patch under
> discussion. It applies to 8.2.5 and the resultant code compiles and runs. I've
> left in some unneeded parallel stuff (PathLocus struct), which I can weed out
> in a subsequent version if desired. I also removed the 'cdb ' from most of the
> function names and (I hope) any Greenplum copyrights.

Thanks, I'll take a look at it.

> I discovered that the patch solves a slightly different problem... it pulls up
> index scans as a viable path choice, (but not for the DESC case) but does not
> push down the LIMIT to the child tables ... so the actual performance
> improvement is zero - however hopefully the patch provides useful raw material
> to help.

> SET
> part=# explain SELECT * FROM n_traf ORDER BY date_time LIMIT 1;
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=198367.14..198367.15 rows=1 width=20)
> -> Sort (cost=198367.14..200870.92 rows=1001510 width=20)
> Sort Key: public.n_traf.date_time
> -> Result (cost=0.00..57464.92 rows=1001510 width=20)
> -> Append (cost=0.00..57464.92 rows=1001510 width=20)
> -> Index Scan using n_traf_date_time_login_id on n_traf
> (cost=0.00..66.90 rows=1510 width=20)

That looks suspicious. There's likely no good reason to be using the index
scan unless it avoids the sort node above the Append node. That's what I hope
to do by having the Append executor code do what's necessary to maintain the
order.

From skimming your patch previously I thought the main point was when there
was only one subnode. In that case it was able to pull the subnode entirely
out of the append node and pull up the paths of the subnode. In Postgres that
would never happen because constraint exclusion will never be able to prune
down to a single partition because of the parent table problem but I expect
we'll change that.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning