Re: Correlation in pg_stats - Mailing list pgsql-general

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