Thread

1. strange query plan

   Mario Weilguni <mweilguni@sime.com> — 2001-04-08T08:35:00Z

   I've a table containing some sort of logs, the table layout ist:
    Attribute |           Type           |               Modifier
   -----------+--------------------------+---------------------------------------
    id        | integer                  | not null default nextval('ids'::text)
    serverid  | integer                  | not null
    ts        | timestamp with time zone | default now()
    log       | character varying(400)   | not null
   Indices: i1,
            log_pkey
   
   primary key is id, and there is an additional index on serverid. The table 
   contains ~ 1.4 mio of rows, I've used "vacuum analyze".
   
   Now when I type:
   explain select min(id)from log;
   NOTICE:  QUERY PLAN:
   
   Aggregate  (cost=45702.20..45702.20 rows=1 width=4)
     ->  Seq Scan on log  (cost=0.00..41978.36 rows=1489536 width=4)
   
   I don't understand why the index log_pkey is not used here. I guess it would 
   be much cheaper to consult the index to get min(), max(), sum() and avg() 
   instead of sequentially scanning ~500MB of data.
   
   Any idea?
   
   Thanks
   
   Best regards,
   	Mario Weilguni
   
   
   -- 
   ===================================================
    Mario Weilguni                               KPNQwest Austria GmbH
    Senior Engineer Web Solutions                         Nikolaiplatz 4
    tel: +43-316-813824                                8020 graz, austria
    fax: +43-316-813824-26                    http://www.kpnqwest.at
    e-mail: mario.weilguni@kpnqwest.com
   ===================================================
   
   

2. Re: strange query plan

   Peter Eisentraut <peter_e@gmx.net> — 2001-04-08T11:50:44Z

   Mario Weilguni writes:
   
   > Now when I type:
   > explain select min(id)from log;
   > NOTICE:  QUERY PLAN:
   >
   > Aggregate  (cost=45702.20..45702.20 rows=1 width=4)
   >   ->  Seq Scan on log  (cost=0.00..41978.36 rows=1489536 width=4)
   >
   > I don't understand why the index log_pkey is not used here. I guess it would
   > be much cheaper to consult the index to get min(), max(), sum() and avg()
   > instead of sequentially scanning ~500MB of data.
   
   Since your query reads the entire "log" table, it is certainly better to
   use a sequential scan in the case of sum() and avg().  A min() and max()
   would theoretically not need to scan the entire table in the presence of
   an index, but unfortunately this doesn't work yet.
   
   -- 
   Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/
   
   
   

3. Info for Solaris-FAQ and prob Unixware-FAQ

   Justin Clift <jclift@iprimus.com.au> — 2001-04-08T15:06:39Z

   Hi Peter,
   
   I've been testing the compilation process of 7.1RC3 with the newest
   release of OpenSSL (0.9.6a) today.  The des_encrypt() namespace conflict
   is no longer present on Solaris 8 (SPARC nor INTEL).  So it looks like
   the OpenSSL guys have renamed the conflicting functions.
   
   Is this something worth mentioning in the Solaris-FAQ (and probably the
   Unixware-FAQ)?
   
   Regards and best wishes,
   
   Justin Clift
   
   

4. Re: Info for Solaris-FAQ and prob Unixware-FAQ

   Peter Eisentraut <peter_e@gmx.net> — 2001-04-08T16:47:29Z

   Justin Clift writes:
   
   > I've been testing the compilation process of 7.1RC3 with the newest
   > release of OpenSSL (0.9.6a) today.  The des_encrypt() namespace conflict
   > is no longer present on Solaris 8 (SPARC nor INTEL).  So it looks like
   > the OpenSSL guys have renamed the conflicting functions.
   >
   > Is this something worth mentioning in the Solaris-FAQ (and probably the
   > Unixware-FAQ)?
   
   Indeed.  Thanks.
   
   -- 
   Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/