Обсуждение: question about efficiency

Howdy:

Running PostgreSQL 7.2.1 on RedHat Linux 7.2.

Have a question about a query and it's efficiency:

One of the users created a query that looks like this:

[snip]
"create table dev_lbpclaimsum as
SELECT
  claimsum2001.c_contract_num,
  claimsum2001.c_mbr_num,
  claimsum2001.c_proc_cd,
  claimsum2001.c_proc_mod,
  claimsum2001.d_from_dt,
  claimsum2001.d_thru_dt,
  claimsum2001.i_pd,
  claimsum2001.c_serv_prov
FROM
  claimsum2001 a join dev_pb_proc b on a.c_proc_cd = substr (b.c_proc_cd,1,6)

[/snip]

After looking monitoring the query, it seems that the job took over a day
and still never completed.  I looked at it and replaced the
'claimsum2001' with 'a' and did an explain on both:

[snip]
[with alias 'a' ]
Nested Loop  (cost=0.00..64051744.91 rows=15892944 width=84)
  ->  Seq Scan on dev_pb_proc b  (cost=0.00..20.00 rows=1000 width=10)
  ->  Index Scan using stateclaim01_proc_cd_i on claimsum2001 a  (cost=0.00..63813.33 rows=15893 width=74)

[/with alias]

[original way]

psql:./jans_stuff.sql:14: NOTICE:  Adding missing FROM-clause entry for table "claimsum2001"
psql:./jans_stuff.sql:14: NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..19629582277130.89 rows=214697805924711 width=95)
  ->  Nested Loop  (cost=0.00..64051744.91 rows=15892944 width=21)
        ->  Seq Scan on dev_pb_proc b  (cost=0.00..20.00 rows=1000 width=10)
        ->  Index Scan using stateclaim01_proc_cd_i on claimsum2001 a  (cost=0.00..63813.33 rows=15893 width=11)
  ->  Seq Scan on claimsum2001  (cost=0.00..1100019.02 rows=13509002 width=74)

EXPLAIN
[/original way]

[/snip]

I created the table in the modified way, it took maybe an hour or so (which is great!)

But, my question is:

Can someone explain why there's a difference between using
claimsum2001.(whatever) and a.(whatever)?  

I wouldn't have thought that it would have mattered in efficiency, just been more convenient.

Thanks!

-X

On Mon, 11 Nov 2002, Johnson, Shaunn wrote:

> Howdy:
>
> Running PostgreSQL 7.2.1 on RedHat Linux 7.2.
>
> Have a question about a query and it's efficiency:
>
> One of the users created a query that looks like this:
>
> [snip]
> "create table dev_lbpclaimsum as
> SELECT
>   claimsum2001.c_contract_num,
>   claimsum2001.c_mbr_num,
>   claimsum2001.c_proc_cd,
>   claimsum2001.c_proc_mod,
>   claimsum2001.d_from_dt,
>   claimsum2001.d_thru_dt,
>   claimsum2001.i_pd,
>   claimsum2001.c_serv_prov
> FROM
>   claimsum2001 a join dev_pb_proc b on a.c_proc_cd = substr
> (b.c_proc_cd,1,6)

This query is (afaik) illegal in plain SQL and for postgres assumes you
want a join with claimsum2001 as claimsum2001. See notice,
> psql:./jans_stuff.sql:14: NOTICE:  Adding missing FROM-clause entry for
> table "claimsum2001"

It's basically converted it into a three way join.

> Can someone explain why there's a difference between using
> claimsum2001.(whatever) and a.(whatever)?

Once you alias claimsum2001 as a there isn't a table claimsum2001
in the query, but you're asking for columns from the non-existant
table in the select clause.  PostgreSQL tries to be helpful by
adding a from entry for the table whereas it'd technically be illegal.

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> It's basically converted it into a three way join.

More to the point, an *unconstrained* join between "claimsum2001
as claimsum2001" and the intended joined table.  The reason it
ran for so long was it was generating a huge number of rows ---
as many as would be in the cross product.

            regards, tom lane

> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
>> It's basically converted it into a three way join.
>
> More to the point, an *unconstrained* join between "claimsum2001
> as claimsum2001" and the intended joined table.  The reason it
> ran for so long was it was generating a huge number of rows ---
> as many as would be in the cross product.

Would it be possible to have some sort of configurable limit on this:

max_result_set_size = 10,000,000
max_result_set_action = ignore | debug | warn | error

The planner knows how many rows it's expecting to return, and a warning
message would show up unconstrained queries like this.

- Richard Huxton

<dev@archonet.com> writes:
> Would it be possible to have some sort of configurable limit on this:

> max_result_set_size = 10,000,000
> max_result_set_action = ignore | debug | warn | error

> The planner knows how many rows it's expecting to return,

Personally, I would never trust the planner's estimate far enough
to use it as the basis for triggering an error ;-)

            regards, tom lane