On 27/11/12 14:23, classical_89 wrote:
Thanks , i just want to get a correlation of near 0 to understand exactly
what correlation , i quite ambiguity about this concept . /
Statistical correlation between physical row ordering and logical ordering
of the column values. This ranges from -1 to +1. When the value is near -1
or +1, an index scan on the column will be estimated to be cheaper than when
it is near zero, due to reduction of random access to the disk. (This column
is null if the column data type does not have a < operator.)
/
I can not explain exactly what is/ physical row ordering and logical
ordering/ of the column values , can you explain to me with an simple
example ??
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Correlation-in-pg-stats-tp5733524p5733655.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Logical ordering depends on what you want it to be. Typically
the order of records according to some index, often the order
defined
by the Primary Key. Some people might think of the order in
which records are inserted - this is mucky: inserts, deletes, and
updates may change the actual ordering on disk.
The physical
order is the way records are stored on disk, and within each disk
block. The disk blocks will not necessarily be arranged in any
particular order, as Postgres will have its own notion as to what
is
efficient and practical (or simply expedient!). Even if you
knew how it did it in one version of Postgres, there is no
requirement for a new version of Postgres to do it in the same
way.
Note that the order records are returned by Postgres, may
be neither the logical order nor a logical order! As Postgres
will return them in any order it deems fit, unless you specify an
ORDER BY statement.
One of the reasons is that it may
have some pages in memory of that table due to some other query,
so
these records might be returned before records that need to be
fetched from the hard disk. Another reason is that your query is
reading in the whole table, and started half way through someone
else's query is reading in the same table - so Postgres is likely
to
return records that are in memory from the other query continuing
as
they are found.
Note that Postgres will endeavour to return
your records in the most efficient manner it can, due to
transaction
isolation and other factors, the order is likely to be
unpredictable
and inconsistent from run to run in a heavily used database.
Why
should Postgres bother to order records in any particular way,
unless
the user has explicitly requested an order?
Cheers,
Gavin
