Date: Tue, 7 Jan 2003 13:39:57 -0200 (GMT+2)
From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-performance@postgresql.org, <pgsql-general@postgresql.org>,
	<pgsql-sql@postgresql.org>
Subject: 7.3.1 index use / performance
Message-ID: <Pine.LNX.4.44.0301071338480.7770-100000@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

Hi,

i am just in the stage of having migrated my test system to 7.3.1
and i am experiencing some performance problems.

i have a table "noon"
                     Table "public.noon"
         Column         |          Type          | Modifiers
------------------------+------------------------+-----------
 v_code                 | character varying(4)   |
 log_no                 | bigint                 |
 report_date            | date                   |
 report_time            | time without time zone |
 voyage_no              | integer                |
 charterer              | character varying(12)  |
 port                   | character varying(24)  |
 duration               | character varying(4)   |
 rotation               | character varying(9)   |
......

with a total of 278 columns.

it has indexes:
Indexes: noonf_date btree (report_date),
         noonf_logno btree (log_no),
         noonf_rotation btree (rotation text_ops),
         noonf_vcode btree (v_code),
         noonf_voyageno btree (voyage_no)

On the test 7.3.1 system (a FreeBSD 4.7-RELEASE-p2, Celeron 1.2GHz
400Mb, with 168Mb for pgsql),
i get:
dynacom=# EXPLAIN ANALYZE select
FUELCONSUMPTION,rpm,Steam_Hours,voyage_activity,ldin from noon where
v_code='4500' and rotation='NOON     ' and report_date between
'2002-01-07' and '2003-01-07';
                                                    QUERY PLAN

-------------------------------------------------------------------------------------------------------------------
 Index Scan using noonf_date on noon  (cost=0.00..4.46 rows=1 width=39)
(actual time=0.27..52.89 rows=259 loops=1)
   Index Cond: ((report_date >= '2002-01-07'::date) AND (report_date <=
'2003-01-07'::date))
   Filter: ((v_code = '4500'::character varying) AND (rotation = 'NOON
'::character varying))
 Total runtime: 53.98 msec
(4 rows)

after i drop the noonf_date index i actually get better performance
cause the backend uses now the more appropriate index noonf_vcode :

dynacom=# EXPLAIN ANALYZE select
FUELCONSUMPTION,rpm,Steam_Hours,voyage_activity,ldin from noon where
v_code='4500' and rotation='NOON     ' and report_date between
'2002-01-07' and '2003-01-07';
                                                               QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using noonf_vcode on noon  (cost=0.00..3122.88 rows=1
width=39) (actual time=0.16..13.92 rows=259 loops=1)
   Index Cond: (v_code = '4500'::character varying)
   Filter: ((rotation = 'NOON     '::character varying) AND (report_date
>= '2002-01-07'::date) AND (report_date <= '2003-01-07'::date))
 Total runtime: 14.98 msec
(4 rows)

On the pgsql 7.2.3 development system (a RH linux 2.4.7, PIII 1 GHz,
1Mb, with 168M for pgsql), i always get the right index use:

dynacom=# EXPLAIN ANALYZE select
FUELCONSUMPTION,rpm,Steam_Hours,voyage_activity,ldin from noon where
v_code='4500' and rotation='NOON     ' and report_date between
'2002-01-07' and '2003-01-07';
NOTICE:  QUERY PLAN:

Index Scan using noonf_vcode on noon  (cost=0.00..3046.38 rows=39
width=39) (actual time=0.09..8.55 rows=259 loops=1)
Total runtime: 8.86 msec

EXPLAIN

Is something i am missing??
Is this reasonable behaviour??

P.S.
Yes i have vaccumed analyzed both systems before the queries were issued.
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-10-8981112
fax:    +30-10-8981877
email:  achill@matrix.gatewaynet.com
        mantzios@softlab.ece.ntua.gr