Re: No index maximum? (was Re: [HACKERS] No merge sort?)
On Mon, Mar 17, 2003 at 11:23:47AM -0600, Taral wrote: > Yes, that's exactly it. It's an index _scan_. It should simply be able > to read the maximum straight from the btree. Still doesn't work, even with rewritten query. It sort a Limit(Sort(Index Scan)), with 1333 rows being pulled from the index. -- Taral <[EMAIL PROTECTED]> This message is digitally signed. Please PGP encrypt mail to me. "Most parents have better things to do with their time than take care of their children." -- Me pgp0.pgp Description: PGP signature
Re: No index maximum? (was Re: [HACKERS] No merge sort?)
On Mon, Mar 17, 2003 at 11:23:47 -0600, Taral <[EMAIL PROTECTED]> wrote: > On Sat, Mar 15, 2003 at 09:23:28AM -0600, Bruno Wolff III wrote: > > On Fri, Mar 14, 2003 at 14:19:46 -0600, > > Taral <[EMAIL PROTECTED]> wrote: > > > Same setup, different query: > > > > > > test=> explain select max(time) from test where id = '1'; > > > NOTICE: QUERY PLAN: > > > > > > Aggregate (cost=5084.67..5084.67 rows=1 width=0) > > > -> Index Scan using idx on test (cost=0.00..5081.33 rows=1333 width=0) > > > > > > Since the index is (id, time), why isn't the index being used to > > > retrieve the maximum value? > > > > It looks like an index scan is being done. > > > > If the index was on (time, id) instead of (id, time), then you could get > > a further speed up by rewriting the query as: > > select time from test where id = '1' order by time desc limit 1; > > Yes, that's exactly it. It's an index _scan_. It should simply be able > to read the maximum straight from the btree. max and min don't use indexes. They are generic aggregate functions and postgres doesn't have the special knowledge to know that for those aggregate functions and index can be used. You can get around this by rewriting the query as I previously indicated. For more details on why things are this way, search the archives. This topic comes up a lot. I was also mistaken about have to switch the index around for this case. It should work the way you have it (if you rewrite the query). ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: No index maximum? (was Re: [HACKERS] No merge sort?)
On Sat, Mar 15, 2003 at 09:23:28AM -0600, Bruno Wolff III wrote: > On Fri, Mar 14, 2003 at 14:19:46 -0600, > Taral <[EMAIL PROTECTED]> wrote: > > Same setup, different query: > > > > test=> explain select max(time) from test where id = '1'; > > NOTICE: QUERY PLAN: > > > > Aggregate (cost=5084.67..5084.67 rows=1 width=0) > > -> Index Scan using idx on test (cost=0.00..5081.33 rows=1333 width=0) > > > > Since the index is (id, time), why isn't the index being used to > > retrieve the maximum value? > > It looks like an index scan is being done. > > If the index was on (time, id) instead of (id, time), then you could get > a further speed up by rewriting the query as: > select time from test where id = '1' order by time desc limit 1; Yes, that's exactly it. It's an index _scan_. It should simply be able to read the maximum straight from the btree. -- Taral <[EMAIL PROTECTED]> This message is digitally signed. Please PGP encrypt mail to me. "Most parents have better things to do with their time than take care of their children." -- Me pgp0.pgp Description: PGP signature
Re: No index maximum? (was Re: [HACKERS] No merge sort?)
On Fri, Mar 14, 2003 at 14:19:46 -0600, Taral <[EMAIL PROTECTED]> wrote: > Same setup, different query: > > test=> explain select max(time) from test where id = '1'; > NOTICE: QUERY PLAN: > > Aggregate (cost=5084.67..5084.67 rows=1 width=0) > -> Index Scan using idx on test (cost=0.00..5081.33 rows=1333 width=0) > > Since the index is (id, time), why isn't the index being used to > retrieve the maximum value? It looks like an index scan is being done. If the index was on (time, id) instead of (id, time), then you could get a further speed up by rewriting the query as: select time from test where id = '1' order by time desc limit 1; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
