Re: Correlation in pg_stats

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