Re: check sql progress

--Thanks for the reply:

--I will try the 'vacuum analyze' on the tables now.

--As far as:

[snip code]

where cast(a.member_id_legacy_cust as char) = b.bp_leg_cust_memb_id;

[/snip]

--The reason I did that was because "a.member_id_legacy_cust" is something
from an MS Access table and the definitions of the columns were different:

[table of t_bp_test1]

Table "t_bp_test1"
Attribute | Type | Modifier
--------------------------+------------------------+----------
prod_id | integer |
member_id | character varying(254) |
member_id_consist | character varying(254) |
member_id_legacy | character varying(254) |
member_id_legacy_consist | character varying(254) |
member_id_legacy_cust | character varying(254) |
members_consist | integer

[/table]

[table of t_bp_stage8]

Table "t_bp_stage8"
Attribute | Type | Modifier
---------------------+-----------+----------
bp_disease_id | text |
bp_measure_id | text |
bp_end_period_id | text |
bp_leg_cust_memb_id | character |
bp_source_code | text |

[/table]

--I just thought it would be easier to CAST to char.
Maybe that's what's the holdup ... but if I didn't have
cast, then I get an error:

[error without using CAST]

psql:./test2:17: ERROR: Unable to identify an operator '='
for types 'varchar' and 'bpchar'

You will have to retype this query using an explicit cast

[/error]

--Maybe I'm going about this the wrong way - could I join
or intersect and get the right results? I wonder ...

--Or, maybe create a new table with new column definitions ...
but that seems to be counterproductive ... I guess ...

--Thanks again!

-X

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]

"Johnson, Shaunn" <SJohnson6(at)bcbsm(dot)com> writes:
> When I do and explain, I get this:

> Nested Loop (cost=0.00..32520.00 rows=10000 width=84)
> -> Seq Scan on t_bp_test1 a (cost=0.00..20.00 rows=1000 width=24)
> -> Seq Scan on t_bp_stage8 b (cost=0.00..20.00 rows=1000 width=60)

> I started the job from last night (about 6pm) and had to
> kill it when I got in this morning (8am). I know the
> data is large (t_bp_stage8 = 8183745 records;
> t_bp_test1 =1677375 records)

The planner evidently doesn't know that. Have you ever done a VACUUM
(perhaps better, VACUUM ANALYZE) on these tables?

Also, I'd suggest simplifying the where clause:

where cast(a.member_id_legacy_cust as char) = b.bp_leg_cust_memb_id;

Can't that be just

where a.member_id_legacy_cust = b.bp_leg_cust_memb_id;

? I think that the added cast will probably prevent the planner from
using any intelligent query plans, like merge or hash join.

regards, tom lane