On Tue, 7 Jan 2003, Tom Lane wrote:
> Achilleus Mantzios <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
[EMAIL PROTECTED]
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)
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
