Thread: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
On 11/7/07 10:21 PM, "Gregory Stark" <stark@enterprisedb.com> wrote: >> 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. Yah - the way it works in GPDB is that you get a non-sorting plan with an index scan below the parent - that was the point of the fix. Hmm. - Luke
Luke Lonergan wrote: > On 11/7/07 10:21 PM, "Gregory Stark" <stark@enterprisedb.com> wrote: > > >>> 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. >> > > Yah - the way it works in GPDB is that you get a non-sorting plan with an > index scan below the parent - that was the point of the fix. Hmm. > > Unfortunately our plan in GPDB looks exactly the same in this case - so we have a bit of work to do as well! Initially I wondered if I have got something wrong in the patch... and checked on GPDB - only to see the same behaviour! (see prev comment about LIMIT). Cheers Mark