Re: No index maximum? (was Re: [HACKERS] No merge sort?)

2003-03-23 Thread Taral 
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?)

2003-03-17 Thread Taral 
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?)

2003-03-17 Thread Bruno Wolff III 
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?)

2003-03-15 Thread Bruno Wolff III 
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

No index maximum? (was Re: [HACKERS] No merge sort?)

2003-03-14 Thread Taral 
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?

On Thu, Mar 13, 2003 at 03:10:49PM -0600, Taral wrote:
 I have a table test that looks like this:
 
 CREATE TABLE test (
 id BIGINT,
 time INTEGER
 );
 
 There is an index:
 
 CREATE INDEX idx ON test(id, time);
 
 The table has been loaded with 2M rows, where time ranges sequentially
 from 0 to 199 and id is random values from 0 to 4.

-- 
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