Re: Query optimizer 8.0.1 (and 8.0) - Mailing list pgsql-hackers

> pgsql@mohawksoft.com writes:
>> I suspect that analyze only samples a very small amount of the database
>> and gets the wrong idea about it. Is there a way to force analyze to
>> sample more  rows?
>
> default_statistics_target.  But let's see the pg_stats rows for these
> columns before assuming that analyze is getting it wrong.

Some more info:

I did a select count(distinct(tlid)) from rt2, and updated the statistics
with  the result:

tiger=# update pg_statistic set stadistinct = 23656799 where starelid =
17236 and staattnum = 1;
UPDATE 1

tiger=# explain select * from rt1, rt2 where rt1.tlid = rt2.tlid and (zipr
tiger(# = 2186 or zipl=2186);                                         QUERY PLAN
-----------------------------------------------------------------------------------------------Nested Loop
(cost=0.00..425517.95rows=21315 width=520)  ->  Index Scan using rt1_zipr, rt1_zipl on rt1  (cost=0.00..121893.93
 
rows=30835 width=302)        Index Cond: ((zipr = 2186) OR (zipl = 2186))  ->  Index Scan using rt2_tlid on rt2
(cost=0.00..9.82rows=2 width=218)        Index Cond: ("outer".tlid = rt2.tlid)
 
(5 rows)
tiger=#     SELECT attname, n_distinct, most_common_vals FROM pg_stats
WHERE tablename = 'rt1'; attname  | n_distinct |                                                 most_common_vals

-----------+------------+-----------------------------------------------------------------------------------------------------------------------tlid
    |         -1 |side1     |          1 | {1}source    |          9 | {B,J,A,K,L,N,O,M,C}fedirp    |          8 |
{N,E,S,W,SW,NE,NW,SE}fename   |       2590 | {Main,1st,Oak,2nd,9th,"Burlington Northern Santa
 
Fe R",Park,4th,11th,8th}fetype    |         26 | {Rd,St,Ave,Dr}fedirs    |          9 | {NE,SE,N,E,NW,SW,W,S,O}cfcc
|         51 | {A41,H12,H11,F10,A74,A31,H01}fraddl    |        767 | {1,101,2,201,301,401,100,701,298,500}toaddl    |
    805 | {199,399,299,499,98,198,99,1,100,2}fraddr    |        765 | {2,1,100,200,400,300,700,101,501,299}toaddr    |
     772 | {198,398,298,498,99,98,199,101,1,1098}friaddl   |          3 | {0,1,2}toiaddl   |          3 |
{0,1,2}friaddr  |          3 | {0}toiaddr   |          3 | {0}zipl      |        925 |zipr      |        899 |aianhhfpl
|        42 |aianhhfpr |         43 |aihhtlil  |          2 |aihhtlir  |          2 |statel    |         55 |
{48,06,12,37,29,42,17,36,13,39}stater   |         55 | {48,06,12,37,29,42,17,36,13,39}countyl   |        189 |
{005,059,013,029,003,001,017,009,031,043}countyr  |        191 | {005,059,013,001,003,029,017,025,031,043}cousubl   |
   2568 |
 
{92601,91800,90270,90240,90468,90572,91508,91750,60000,90324}cousubr   |       2598 |
{92601,91800,90270,90240,90468,90572,91248,91750,60000,90324}submcdl   |         -1 |submcdr   |         -1 |placel
|       778 |
 
{51000,65000,44000,12000,38000,60000,63460,07000,04000,22000}placer    |        787 |
{51000,65000,12000,44000,60000,07000,38000,55000,63460,04000}tractl    |       1370 |
{950200,950100,950300,000100,970100,960100,980100,950700,970300,990100}tractr    |       1354 |
{950200,950100,950300,000100,970100,960100,990100,950700,980100,970300}blockl    |       1050 |
{1000,2000,1001,1003,1005,2001,1009,2006,1002,1004}blockr   |       1055 |
{1000,2000,1001,1002,1003,1005,1004,1009,2004,2002}frlong   |     134476 |
 

{-120.214657,-113.074100,-106.494480,-103.306945,-100.184470,-100.083614,-99.476994,-98.420248,-97.325498,-93.349865}frlat
   |     143222 |
 
{27.759896,29.251454,29.898585,30.093247,31.814071,31.950913,32.055726,32.377503,32.523607,32.607387}tolong    |
317744|
 

{-123.330861,-111.673035,-107.596898,-103.164000,-100.945693,-100.080307,-99.576886,-99.492719,-97.743722,-93.870222}tolat
   |     278079 |
 
{27.493816,27.904316,29.691644,32.731410,33.350429,34.490563,35.551053,35.868297,39.139185,40.068098}
(40 rows)