Re: [PERFORM] query produces 1 GB temp file

[Dirk Lutzebaeck]

Greg Stark wrote:
I gave a bunch of "explain analyze select" commands to test estimates for
individual columns. What results do they come up with? If those are inaccurate
then raising the statistics target is a good route. If those are accurate
individually but the combination is inaccurate then you have a more difficult
problem.
 

After  setting the new statistics target to 200 they did slightly better 
but not accurate. The results were attached to my last post. Here is a copy:


explain analyze select * from bi where rc=130170467;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------- 

Seq Scan on bi (cost=0.00..41078.76 rows=190960 width=53) (actual 
time=0.157..3066.028 rows=513724 loops=1)
Filter: (rc = 130170467::oid)
Total runtime: 4208.663 ms
(3 rows)

explain analyze select * from bi where co=117305223;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------- 

Seq Scan on bi (cost=0.00..41078.76 rows=603988 width=53) (actual 
time=0.021..3692.238 rows=945487 loops=1)
Filter: (co = 117305223::oid)
Total runtime: 5786.268 ms
(3 rows)

Here is the distribution of the data in bi:
select count(*) from bi;
1841966
select count(*) from bi where rc=130170467::oid;
513732
select count(*) from bi where co=117305223::oid;
945503


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to [EMAIL PROTECTED] so that your
     message can get through to the mailing list cleanly