Re: enable_sort optimization problem

tom lane wrote:

>Why does it think that only 159 of the 132245 rows in outages will have
>join partners in ipinterface? The actual results look like they all do.
>It might be worth looking at the pg_stats rows for the join columns to
>see if there's something odd about the statistics.
>

Here are the pg_stats (as of today, I haven't done any analyzes or
vacuums since the night of my first posting) for outages and ipinterface
(I've obscured some addresses in the ipiddr row, and removed iphostname):

schemaname | tablename | attname | null_frac | avg_width |
n_distinct
|
most_common_vals
|
most_common_freqs
|
histogram_bounds
| correlation
------------+-----------+--------------------+-------------+-----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
public | outages | outageid | 0 | 4
| -1
|
|
|
{201,14775,27621,39600,53231,66043,79629,92779,105267,119744,134644}
| 0.390484
public | outages | svclosteventid | 0 | 4
| -0.945011 |
{280277,356772}
|
{0.000666667,0.000666667}
|
{17842,54830,81745,107030,135793,163240,191819,219223,268449,309128,371234}
| 0.390484
public | outages | svcregainedeventid | 0.000333333 | 4
| -0.958031 |
{280279}
|
{0.000666667}
|
{17844,54856,81787,107063,135821,163381,191856,219405,268450,309572,371494}
| 0.390818
public | outages | nodeid | 0 | 4
| 396 |
{962,397,191,185,377,139,237,378,1295,231}
|
{0.0173333,0.00966667,0.00866667,0.00833333,0.00833333,0.00766667,0.00766667,0.00766667,0.00766667,0.007}
|
{3,158,178,206,236,258,293,316,358,395,1452}
| -0.0325868
public | outages | ipaddr | 0 | 16
| 396 |
{208....,172.22.0.158,172.20.0.237,172.20.0.231,172.22.35.56,172.17.2.5,172.20.0.180,172.21.240.91,172.23.181.16,172.21.240.93}
|
{0.0173333,0.00966667,0.00866667,0.00833333,0.00833333,0.00766667,0.00766667,0.00766667,0.00766667,0.007}
|
{172.16.156.43,172.20.0.202,172.20.0.225,172.20.0.9,172.22.0.146,172.22.240.151,172.22.240.180,172.22.35.30,172.23.45.17,207...,209....}
| -0.0116046
public | outages | serviceid | 0 | 4
| 10 |
{9}
|
{0.87}
|
{1,1,1,1,1,1,11,14,23}
| 0.781509
public | outages | iflostservice | 0 | 8
| -0.351555 | {"2005-04-12 18:27:16","2005-04-13 16:26:04","2005-04-13
17:49:33","2005-04-13 18:45:26","2005-04-13 20:07:07","2005-04-13
20:29:17","2005-04-17 01:34:25","2005-04-22 19:56:45","2005-04-11
14:32:39","2005-04-12 14:02:14"} |
{0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.000666667,0.000666667}
| {"2005-04-07 20:23:28","2005-04-12 14:51:23","2005-04-12
22:14:25","2005-04-13 05:10:24","2005-04-13 13:01:27","2005-04-13
20:51:31","2005-04-14 04:35:32","2005-04-14 11:40:45","2005-04-24
15:53:49","2005-05-14 13:57:16","2005-05-26 06:13:01"} | 0.390484
public | outages | ifregainedservice | 0 | 8
| -0.332081 | {"2005-04-11 14:33:51","2005-04-12 18:27:47","2005-04-13
16:26:35","2005-04-13 17:49:33","2005-04-13 20:07:46","2005-04-13
20:29:58","2005-04-17 01:34:56","2005-04-12 12:19:17","2005-04-12
14:02:45","2005-04-12 16:07:00"} |
{0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.000666667,0.000666667,0.000666667}
| {"2005-04-07 20:23:59","2005-04-12 14:55:59","2005-04-12
22:19:32","2005-04-13 05:16:01","2005-04-13 13:10:44","2005-04-13
20:56:58","2005-04-14 04:43:27","2005-04-14 11:44:15","2005-04-24
15:54:20","2005-05-14 13:58:35","2005-05-26 09:18:35"} | 0.390543
(8 rows)

schemaname | tablename | attname | null_frac | avg_width |
n_distinct
|
most_common_vals
|
most_common_freqs
|
histogram_bounds
| correlation
------------+-------------+-----------------+-----------+-----------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
public | ipinterface | nodeid | 0 | 4 |
-0.760204 |
{383,195,132,149,380,381,382,1138,1142,1296}
|
{0.0349854,0.0189504,0.0174927,0.0174927,0.0174927,0.0174927,0.0174927,0.0153061,0.0153061,0.0123907}
|
{1,92,205,291,396,694,920,1031,1144,1352,1455}
| 0.405376
public | ipinterface | ipaddr | 0 | 16 |
-0.773324 |
{0.0.0.0,10.0.1.1}
|
{0.225948,0.00218659}
|
{172.16.156.1,172.21.240.3,172.22.35.30,207...,207...,207...,208...,208....,208....,209....,216...}
| 0.14107
public | ipinterface | ifindex | 0.706997 | 4
| 51 |
{1,-100}
|
{0.0327988,0.0262391}
|
{2,3,4,6,8,11,14,18,21,105,16777219}
| 0.111678
public | ipinterface | ismanaged | 0 | 5
| 3 |
{M,U,F}
|
{0.686589,0.225948,0.0874636}
|
| 0.503811
public | ipinterface | ipstatus | 0.733236 | 4
| 2 |
{1,2}
|
{0.228134,0.0386297}
|
| 0.836075
public | ipinterface | iplastcapsdpoll | 0 | 8 |
-0.765306 | {"2005-05-25 12:24:27.161","2005-05-25
11:41:20.384","2005-05-25 14:01:54.991","2005-05-25
14:30:18.367","2005-05-26 03:38:57.83","2005-04-18
14:12:28.958","2005-04-25 06:57:51.826","2005-05-03
19:39:53.817","2005-05-12 20:31:11.786","2005-05-12 20:41:40.77"} |
{0.0182216,0.0174927,0.0174927,0.0174927,0.0174927,0.0167638,0.0167638,0.0167638,0.0153061,0.0153061}
| {"2005-04-22 03:38:21.991","2005-05-12 20:38:13.719","2005-05-25
13:08:05.957","2005-05-25 16:33:14.61","2005-05-25
18:26:20.926","2005-05-25 19:33:52.111","2005-05-25
22:07:54.466","2005-05-25 23:14:54.804","2005-05-26
02:54:39.604","2005-05-26 07:29:45.067","2005-05-26 11:49:17.825"} |
-0.0102781
public | ipinterface | issnmpprimary | 0.0495627 | 5
| 4 |
{N,P,C,S}
|
{0.868805,0.053207,0.021137,0.00728863}
|
| 0.871739
(8 rows)