Skip site navigation (1) Skip section navigation (2)

Optimizer fails?

From: Michal Mosiewicz <mimo(at)interdata(dot)com(dot)pl>
To: hackers(at)postgresql(dot)org
Subject: Optimizer fails?
Date: 1998-03-25 03:17:18
Message-ID: 3518773E.3EC4D592@interdata.com.pl (view raw or flat)
Thread:
Lists: pgsql-hackers
xxxx=> \d logdt

Table    = logdt
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+
| dt                               | timestamp                       
|     4 |
+----------------------------------+----------------------------------+-------+
xxxx=> explain select * from log where dt < '19980203';
NOTICE:  QUERY PLAN:

Seq Scan on log  (cost=105832.15 size=699588 width=62)

There is an index on log table, dt field. The index is b-tree.
However it doesn't seem to be used. (Of course I have vacuumed it). The
table has about 2M records. I don't think that Seq Scan is a good idea.

Also, what if I agregate it on dt field to count(*) or sum some values.
It would be sequentially scanned, then sorted, then grouped and finally
agregated, right?

Well, sometimes it may be good enough. But if this table is big enough,
it would be wiser to use index to select ranges from the table and then
agregate those values without sorting. 

Once I saw index based agregates in the TODO list, but somehow it
disappeared.

Regards,
Mike

-- 
WWW: http://www.lodz.pdi.net/~mimo  tel: Int. Acc. Code + 48 42 148340
add: Michal Mosiewicz  *  Bugaj 66 m.54 *  95-200 Pabianice  *  POLAND

Responses

pgsql-hackers by date

Next:From: Vadim B. MikheevDate: 1998-03-25 03:43:44
Subject: Re: AW: [HACKERS] Begin statement again
Previous:From: Jonathan GuthrieDate: 1998-03-25 02:58:06
Subject: Re: [HACKERS] Postgres "in the field"

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group