From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: pgsql-performance@postgresql.org
Cc: pgsql-general@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] [PERFORM] 7.3.1 index use / performance
Date: Tue, 7 Jan 2003 19:05:22 -0200 (GMT+2)
Message-ID: <Pine.LNX.4.44.0301071856280.9324-200000@matrix.gatewaynet.com> (raw)
In-Reply-To: <13843.1041956988@sss.pgh.pa.us>
On Tue, 7 Jan 2003, Tom Lane wrote:
> Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> > About the stats on these 3 columns i get:
>
> Does 7.2 generate the same stats? (minus the schemaname of course)
Not absolutely but close:
(See attachment)
>
> Also, I would like to see the results of these queries on both versions,
> so that we can see what the planner thinks the index selectivity is:
>
> EXPLAIN ANALYZE select * from noon where
> v_code='4500';
>
> EXPLAIN ANALYZE select * from noon where
> report_date between '2002-01-07' and '2003-01-07';
>
On 7.3.1 (On a FreeBSD)
=======================
dynacom=# EXPLAIN ANALYZE select * from noon where v_code='4500';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using noonf_vcode on noon (cost=0.00..3066.64 rows=829
width=1974) (actual time=2.02..1421.14 rows=792 loops=1)
Index Cond: (v_code = '4500'::character varying)
Total runtime: 1424.82 msec
(3 rows)
dynacom=# EXPLAIN ANALYZE select * from noon where report_date between
'2002-01-07' and '2003-01-07';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Index Scan using noonf_date on noon (cost=0.00..15919.50 rows=11139
width=1974) (actual time=2.05..13746.17 rows=7690 loops=1)
Index Cond: ((report_date >= '2002-01-07'::date) AND (report_date <=
'2003-01-07'::date))
Total runtime: 13775.48 msec
(3 rows)
On 7.2.3 (Linux)
==================
dynacom=# EXPLAIN ANALYZE select * from noon where v_code='4500';
NOTICE: QUERY PLAN:
Index Scan using noonf_vcode on noon (cost=0.00..3043.45 rows=827
width=1974) (actual time=19.59..927.06 rows=792 loops=1)
Total runtime: 928.86 msec
dynacom=# EXPLAIN ANALYZE select * from noon where report_date between
'2002-01-07' and '2003-01-07';
NOTICE: QUERY PLAN:
Index Scan using noonf_date on noon (cost=0.00..16426.45 rows=11958
width=1974) (actual time=29.64..8854.05 rows=7690 loops=1)
Total runtime: 8861.90 msec
EXPLAIN
> regards, tom lane
>
==================================================================
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
Attachments:querystats_on_7.2.3 (text/plain, 2.1K) download
tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation
-----------+-------------+-----------+-----------+------------+-----------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+-------------
noon | v_code | 0 | 8 | 109 | {4550,4630,4650,4800,4520,4770,4690,4620,4610,4560} | {0.0273333,0.0263333,0.026,0.0256667,0.0253333,0.0253333,0.0246667,0.0226667,0.0223333,0.022} | {1030,3210,4360,4500,4570,4670,4740,4820,4870,4940,6020} | -0.260377
noon | report_date | 0 | 4 | 3402 | {1999-01-22,2000-12-26,1998-09-29,1998-10-11,1999-02-24,1999-05-19,1999-09-08,1999-09-13,2000-01-19,2000-02-03} | {0.002,0.002,0.00166667,0.00166667,0.00166667,0.00166667,0.00166667,0.00166667,0.00166667,0.00166667} | {"0001-11-07 BC",1994-10-22,1996-04-05,1997-06-19,1998-07-31,1999-04-01,1999-12-15,2000-09-29,2001-05-31,2002-02-06,2003-01-02} | -0.821627
noon | rotation | 0 | 13 | 6 | {"NOON ","PORT LOG ","ARRIVAL ",DEPARTURE,"SEA ","NEXT PORT"} | {0.478,0.253667,0.138333,0.121667,0.006,0.00233333} | | 0.147822
(3 rows)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: achill@matrix.gatewaynet.com, tgl@sss.pgh.pa.us, sszabo@megazone23.bigpanda.com, pgsql-performance@postgresql.org, pgsql-general@postgresql.org
Subject: Re: [SQL] [PERFORM] 7.3.1 index use / performance
In-Reply-To: <Pine.LNX.4.44.0301071856280.9324-200000@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox