Обсуждение: the results from a query - question
Running PostgreSQL 7.2.1 on RedHat Linux 7.2.
Question - I'm looking at one of the users query
[snip query]
explain
select *
-- INTO dev_gm_er_prof01
FROM
db2_gm_reg_prof_01
WHERE
db2_gm_reg_prof_01.place_of_service = 2 and
db2_gm_reg_prof_01.diagnosis_cd not like '29%' and
db2_gm_reg_prof_01.diagnosis_cd not like '30%' and
db2_gm_reg_prof_01.diagnosis_cd not like '310%' and
db2_gm_reg_prof_01.diagnosis_cd not like '311%' and
db2_gm_reg_prof_01.diagnosis_cd not like '312%' and
db2_gm_reg_prof_01.diagnosis_cd not like '313%' and
db2_gm_reg_prof_01.diagnosis_cd not like '314%' and
db2_gm_reg_prof_01.diagnosis_cd not like '315%' and
db2_pos_reg_prof_01.diagnosis_cd not like '316%'
--ORDER BY
-- db2_gm_reg_prof_01.contract_num ASC;
[/snip query]
And doing an EXPLAIN, I come up with this -
[snip explain]
psql:./marsha_2apr.sql:19: NOTICE: Adding missing FROM-clause entry for table "db2_pos_reg_prof_01"
psql:./marsha_2apr.sql:19: NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..391628573.33 rows=4587594094 width=402)
-> Index Scan using db2_gm_prof_pos_01_i on db2_gm_reg_prof_01 (cost=0.00..8298.20 rows=2036 width=402)
-> Seq Scan on db2_pos_reg_prof_01 (cost=0.00..169793.33 rows=2252945 width=0)
[/snip explain]
Is this *really* supposed to bring back 4587594094 rows into this
table they are trying to create? I mean, I see obvious things (like the
file system growing like mad), but I just want to be sure before
I start making wild accusations.
Thanks!
-X
the results from a query - questionShaunn Johnson wrote: <rest of query snipped> db2_gm_reg_prof_01.diagnosis_cd not like '314%' and db2_gm_reg_prof_01.diagnosis_cd not like '315%' and db2_pos_reg_prof_01.diagnosis_cd not like '316%' ^^^^^^^^^^^^^^^^^^ Did you really mean to refer to a different table on the last where clause? Regards Peter Gibbs EmKel Systems
--as i've said, i did an explain and saw that
--part (where the error message says 'adding this to
--the FROM clause).
--i suspect that this was a typo, but since the
--person creating this isn't here now, i can not
--answer this.
--and i'm at the point where i *have* to kill the script.
--i just wanted to make sure that i wasn't just
--out on a witch hunt.
--thanks!
-X
-----Original Message-----
From: Peter Gibbs [mailto:peter@emkel.co.za]
Sent: Wednesday, April 02, 2003 9:22 AM
To: Johnson, Shaunn; pgsql (E-mail)
Subject: Re: [GENERAL] the results from a query - question
the results from a query - questionShaunn Johnson wrote:
<rest of query snipped>
db2_gm_reg_prof_01.diagnosis_cd not like '314%' and
db2_gm_reg_prof_01.diagnosis_cd not like '315%' and
db2_pos_reg_prof_01.diagnosis_cd not like '316%'
^^^^^^^^^^^^^^^^^^
Did you really mean to refer to a different table on the last where clause?
Regards
Peter Gibbs
EmKel Systems
On Wednesday 02 April 2003 16:00, Johnson, Shaunn wrote: > Running PostgreSQL 7.2.1 on RedHat Linux 7.2. > > Question - I'm looking at one of the users query > > [snip query] > explain > select * > -- INTO dev_gm_er_prof01 > FROM > db2_gm_reg_prof_01 > WHERE > db2_gm_reg_prof_01.place_of_service = 2 and > db2_gm_reg_prof_01.diagnosis_cd not like '29%' and > db2_gm_reg_prof_01.diagnosis_cd not like '30%' and > db2_gm_reg_prof_01.diagnosis_cd not like '310%' and > db2_gm_reg_prof_01.diagnosis_cd not like '311%' and > db2_gm_reg_prof_01.diagnosis_cd not like '312%' and > db2_gm_reg_prof_01.diagnosis_cd not like '313%' and > db2_gm_reg_prof_01.diagnosis_cd not like '314%' and > db2_gm_reg_prof_01.diagnosis_cd not like '315%' and > db2_pos_reg_prof_01.diagnosis_cd not like '316%' > --ORDER BY > -- db2_gm_reg_prof_01.contract_num ASC; > > [/snip query] From this query, db2_pos_reg_prof_01 does not seem to be joined explicity to db2_gm_reg_prof_01. This may be producing more rows than you want (cartesian join)... Ian Barwick barwick@gmx.net
"Johnson, Shaunn" <SJohnson6@bcbsm.com> writes: > explain > select * > -- INTO dev_gm_er_prof01 > FROM > db2_gm_reg_prof_01 > WHERE > db2_gm_reg_prof_01.place_of_service = 2 and > db2_gm_reg_prof_01.diagnosis_cd not like '29%' and > db2_gm_reg_prof_01.diagnosis_cd not like '30%' and > db2_gm_reg_prof_01.diagnosis_cd not like '310%' and > db2_gm_reg_prof_01.diagnosis_cd not like '311%' and > db2_gm_reg_prof_01.diagnosis_cd not like '312%' and > db2_gm_reg_prof_01.diagnosis_cd not like '313%' and > db2_gm_reg_prof_01.diagnosis_cd not like '314%' and > db2_gm_reg_prof_01.diagnosis_cd not like '315%' and > db2_pos_reg_prof_01.diagnosis_cd not like '316%' ^^^ Isn't that a typo? > Is this *really* supposed to bring back 4587594094 rows into this > table they are trying to create? If the two tables indeed have ~2K and ~2M rows respectively, then yes, an unconstrained join of the two will yield ~4G rows ... regards, tom lane