7.3.1 index use / performance - Mailing list pgsql-sql
From | Achilleus Mantzios |
---|---|
Subject | 7.3.1 index use / performance |
Date | |
Msg-id | Pine.LNX.4.44.0301071338480.7770-100000@matrix.gatewaynet.com Whole thread Raw |
Responses |
Re: [PERFORM] 7.3.1 index use / performance
7.3.1 function problem: ERROR: cache lookup failed for type 0 |
List | pgsql-sql |
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