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)