Received: from atlrel2.hp.com (atlrel2.hp.com [156.153.255.202]) by hub.org (8.9.3/8.9.3) with ESMTP id XAA33646 for ; Wed, 24 May 2000 23:57:26 -0400 (EDT) (envelope-from ryan_bradetich@hp.com) Received: from hpbs5000.boi.hp.com (hpbs5000.boi.hp.com [15.56.8.201]) by atlrel2.hp.com (Postfix) with ESMTP id EFF5A6D3; Wed, 24 May 2000 23:57:25 -0400 (EDT) Received: from hp.com (rbrad@hpb50023.boise.itc.hp.com [15.62.176.50]) by hpbs5000.boi.hp.com with ESMTP (8.8.6 (PHNE_17135)/8.7.3 TIS 5.0.1) id VAA06530; Wed, 24 May 2000 21:57:25 -0600 (MDT) Message-ID: <392CA4A5.FD06E9AC@hp.com> Date: Wed, 24 May 2000 21:57:25 -0600 From: Ryan Bradetich X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.3.99-pre3 i686) X-Accept-Language: en MIME-Version: 1.0 To: Tom Lane Cc: pgsql-sql@postgreSQL.org Subject: Re: Use of index in 7.0 vs 6.5 References: <392C6056.D5BF7FE8@hp.com> <19295.959224352@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200005/136 Tom Lane wrote: > Ryan Bradetich writes: > > I am in the process of transitioning from postgreSQL 6.5.3 to > > postgreSQL 7.0. I ran into an issue where a sequential scan > > is being choosen on postgreSQL 7.0 where an index scan was > > choosen on postgreSQL 6.5.3. > > Since you're complaining, I assume the seqscan is slower ;-). > But you didn't say how much slower --- what are the actual timings? Opps... Had them written down, just forgot to include them in the email :) with enable_seqscan = on: real 18.05 sys 0.01 user 0.02 with enable_seqscan = off: real 0.08 sys 0.01 user 0.02 I stopped and restarted the postmaster daemon between these timing to flush the cache. > Basically what's going on here is that we need to tune the fudge-factor > constants in the cost model so that they have something to do with > reality on as wide a variety of systems as possible. You did an > excellent job of showing the estimates the planner computed --- but > what we really need here is to see how those relate to reality. > > > I do not understand why the planner would choose a seqscan over the > > index scan because 6704/4,630,229 is ~ 0.15%. > > I'm a bit surprised too. What is the average tuple width on this table? > (Actually, probably a better question is how many pages and tuples > are in the relation according to its pg_class entry. Try "select * from > pgclass where relname = 'medusa'".) > > regards, tom lane procman=# select * from pg_class where relname = 'medusa'; relname | reltype | relowner | relam | relpages | reltuples | rellongrelid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhas pkey | relhasrules | relacl ---------+---------+----------+-------+----------+-----------+--------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------- -----+-------------+-------- medusa | 0 | 36000 | 0 | 120076 | 4630229 | 0 | t | f | r | 6 | 0 | 0 | 0 | 0 | 0 | f | f | (1 row) procman=# \d medusa Table "medusa" Attribute | Type | Modifier -----------+-----------+---------- host_id | integer | timestamp | timestamp | current | integer | catagory | text | cat_desc | text | anomaly | text | This table has two fairly large text fields, the cat_desc and the anomaly. The catagory field is very short and in the format: [ABC][0-9][0-9]. Thanks for the help, - Ryan