From: Mark Kirkwood <markir@paradise.net.nz>
To: chris smith <dmagick@gmail.com>
Cc: Jim C. Nasby <jnasby@pervasive.com>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Query using SeqScan instead of IndexScan
Date: Sun, 02 Apr 2006 16:31:34 +1200
Message-ID: <442F53A6.9090707@paradise.net.nz> (raw)
In-Reply-To: <3c1395330604011732n4cd523c3gf7c1e4c346630aad@mail.gmail.com>
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>
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
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-performance@postgresql.org
Cc: markir@paradise.net.nz, dmagick@gmail.com, jnasby@pervasive.com
Subject: Re: Query using SeqScan instead of IndexScan
In-Reply-To: <442F53A6.9090707@paradise.net.nz>
* 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