Re: query produces 1 GB temp file

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