Date: Tue, 7 Jan 2003 07:27:49 -0800 (PST)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>
Cc: <pgsql-performance@postgresql.org>,
	<pgsql-general@postgresql.org>, <pgsql-sql@postgresql.org>
Subject: Re: [PERFORM] 7.3.1 index use / performance
In-Reply-To: <Pine.LNX.4.44.0301071338480.7770-100000@matrix.gatewaynet.com>
Message-ID: <20030107072146.L61341-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


On Tue, 7 Jan 2003, Achilleus Mantzios wrote:

> 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 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)


What do the statistics for the three columns actually look like and what
are the real distributions and counts like?
Given an estimated cost of around 4 for the first scan, my guess would be
that it's not expecting alot of rows between 2002-01-07 and 2003-01-07
which would make that a reasonable plan.