X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 984BB11F6B3C
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Sun,  2 Apr 2006 00:31:39 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 94602-06
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Sun,  2 Apr 2006 00:31:37 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from linda-4.paradise.net.nz (bm-4a.paradise.net.nz
 [203.96.152.183])
	by postgresql.org (Postfix) with ESMTP id 2646711F6B52
	for <pgsql-performance@postgresql.org>;
	Sun,  2 Apr 2006 00:31:37 -0400 (AST)
Received: from smtp-3.paradise.net.nz
	(tclsnelb1-src-1.paradise.net.nz [203.96.152.172]) by
	linda-4.paradise.net.nz (Paradise.net.nz) with ESMTP id
	<0IX200FJGV8N34@linda-4.paradise.net.nz> for
	pgsql-performance@postgresql.org; Sun, 02 Apr 2006 16:31:35 +1200 (NZST)
Received: from [192.168.1.11]
	(218-101-28-188.dsl.clear.net.nz [218.101.28.188])	by
	smtp-3.paradise.net.nz (Postfix) with ESMTP id BB35716982C9;
	Sun, 02 Apr 2006 16:31:34 +1200 (NZST)
Date: Sun, 02 Apr 2006 16:31:34 +1200
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: Query using SeqScan instead of IndexScan
In-reply-to: <3c1395330604011732n4cd523c3gf7c1e4c346630aad@mail.gmail.com>
To: chris smith <dmagick@gmail.com>
Cc: "Jim C. Nasby" <jnasby@pervasive.com>,
	PostgreSQL Performance <pgsql-performance@postgresql.org>
Message-id: <442F53A6.9090707@paradise.net.nz>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7bit
X-Accept-Language: en-us, en
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20051106)
References: <2F1712F1-E5E2-455E-A291-06F300A66549@clickspace.com>
	<20060331155911.GP49405@pervasive.com>
	<EBEB7ACF-84A8-4FCF-A481-8C087FF372D1@clickspace.com>
	<3c1395330603311723i368092f2jf0c2291ab033ae2a@mail.gmail.com>
	<20060401153247.GI49405@pervasive.com>
	<3c1395330604011650r61c793a7td3e8be58ab023c7b@mail.gmail.com>
	<3c1395330604011732n4cd523c3gf7c1e4c346630aad@mail.gmail.com>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.169 required=5 tests=[AWL=0.169]
X-Spam-Score: 0.169
X-Spam-Level: 
X-Archive-Number: 200604/8
X-Sequence-Number: 18092

chris smith wrote:

> <rant>
> It'd be nice if the database developers agreed on what terms meant.
> 
> http://dev.mysql.com/doc/refman/5.1/en/myisam-index-statistics.html
> 
> The SHOW INDEX statement displays a cardinality value based on N/S,
> where N is the number of rows in the table and S is the average value
> group size. That ratio yields an approximate number of value groups in
> the table.
> </rant>
> 
> A work colleague found that information a few weeks ago so that's
> where my misunderstanding came from - if I'm reading that right they
> use n_distinct as their "cardinality" basis.. then again I could be
> reading that completely wrong too.
> 

Yeah that's right - e.g using the same table in postgres and mysql:

pgsql> SELECT attname,n_distinct,correlation
        FROM pg_stats
        WHERE tablename='fact0'
        AND attname LIKE 'd%key';
  attname | n_distinct | correlation
---------+------------+-------------
  d0key   |      10000 |  -0.0211169
  d1key   |        100 |    0.124012
  d2key   |         10 |    0.998393
(3 rows)


mysql> SHOW INDEX FROM fact0
     -> ;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| fact0 |          1 | fact0_d0key |            1 | d0key       | A
     |       10000 |     NULL | NULL   |      | BTREE      |         |
| fact0 |          1 | fact0_d1key |            1 | d1key       | A
     |         100 |     NULL | NULL   |      | BTREE      |         |
| fact0 |          1 | fact0_d2key |            1 | d2key       | A
     |          10 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)


It is a bit confusing - '(distinct) cardinality' might be a better
heading for their 'cardinality' column!

On the correlation business - I don't think Mysql calculates it (or if
it does, its not displayed).


> I believe postgres (because it's a lot more standards compliant).. but
> sheesh - what a difference!
> 

Well yes - however, to be fair to the Mysql guys, AFAICS the capture and 
display of index stats (and any other optimizer related data) is not 
part of any standard.


Cheers

Mark