Postgres Pro
Downloads
Home   >   mailing lists

Thread: the results from a query - question

the results from a query - question

From
"Johnson, Shaunn"
Date:

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

Re: the results from a query - question

From
"Peter Gibbs"
Date:
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

Re: the results from a query - question

From
"Johnson, Shaunn"
Date:

--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

Re: the results from a query - question

From
Ian Barwick
Date:
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

Re: the results from a query - question

From
Tom Lane
Date:
"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