On 9/13/06, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
"Joshua Marsh" <icub3d@xxxxxxxxx> writes:
>> Are the tables perhaps nearly in order by the dsiacctno fields?
> My assumption would be they are in exact order. The text file I used
> in the COPY statement had them in order, so if COPY preserves that in
> the database, then it is in order.
Ah. So the question is why the planner isn't noticing that. What do
you see in the pg_stats view for the two dsiacctno fields --- the
correlation field in particular?
Here are the results:
data=# select tablename, attname, n_distinct, avg_width, correlation
from pg_stats where tablename in ('view_505', 'r3s169') and attname =
'dsiacctno';
tablename | attname | n_distinct | avg_width | correlation
-----------+-----------+------------+-----------+-------------
view_505 | dsiacctno | -1 | 13 | -0.13912
r3s169 | dsiacctno | 44156 | 13 | -0.126824
(2 rows)
Someone suggested CLUSTER to make sure they are in fact ordered, I can
try that to and let everyone know the results.
> The system has 8GB of ram and work_mem is set to 256MB.
Seems reasonable enough. BTW, I don't think you've mentioned exactly
which PG version you're using?
regards, tom lane
I am using 8.0.3.
