Skip site navigation (1) Skip section navigation (2)

Re: Query optimizer 8.0.1 (and 8.0)

From: pgsql(at)mohawksoft(dot)com
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Query optimizer 8.0.1 (and 8.0)
Date: 2005-02-04 23:50:30
Message-ID: 16409.24.91.171.78.1107561030.squirrel@mail.mohawksoft.com (view raw or flat)
Thread:
Lists: pgsql-hackers
> pgsql(at)mohawksoft(dot)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.95 rows=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.82 rows=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)




In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2005-02-05 00:03:46
Subject: Re: Query optimizer 8.0.1 (and 8.0)
Previous:From: Rolf SponselDate: 2005-02-04 22:26:28
Subject: Re: [Feed-back] Installing PostgreSQL 8.0.0 on SPARC/Solaris.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group