pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
why the big difference on this explain analyze?
4+ messages / 2 participants
[nested] [flat]

* why the big difference on this explain analyze?
@ 2002-03-16 22:07 Dan Langille <dan@langille.org>
  2002-03-16 22:19 ` Re: why the big difference on this explain analyze? Tom Lane <tgl@sss.pgh.pa.us>
  2002-03-16 22:22 ` Re: why the big difference on this explain analyze? Dan Langille <dan@langille.org>
  2002-03-17 00:18 ` Re: why the big difference on this explain analyze? Dan Langille <dan@langille.org>
  0 siblings, 3 replies; 4+ messages in thread

From: Dan Langille @ 2002-03-16 22:07 UTC (permalink / raw)
  To: pgsql-sql

I was trying to get a query to run.  Yes run.  Not run fast.  Just run.   
;) It was taking far too long.

Here is the before:

explain 
fp2migration=#
fp2migration=# explain
fp2migration-# UPDATE Ports_Check SET found_in_ports = TRUE
fp2migration-#         WHERE EXISTS
fp2migration-#             (   SELECT *
fp2migration(#                   FROM categories, element, ports
fp2migration(#                  WHERE ports.category_id = categories.id
fp2migration(#                    AND ports.element_id  = element.id
fp2migration(#                    AND categories.name   = 
Ports_Check.category_name
fp2migration(#                    AND element.name      = 
Ports_Check.port_name);
NOTICE:  QUERY PLAN:

Seq Scan on ports_check  (cost=0.00..7226574.01 rows=3354 width=27)
  SubPlan
    ->  Hash Join  (cost=103.10..1077.13 rows=1 width=1073)
          ->  Hash Join  (cost=101.47..1075.49 rows=2 width=1030)
                ->  Seq Scan on ports  (cost=0.00..938.99 rows=6999 
width=1000)
                ->  Hash  (cost=101.40..101.40 rows=27 width=30)
                      ->  Index Scan using element_name on element  
(cost=0.00..101.40 rows=27 width=30)
          ->  Hash  (cost=1.62..1.62 rows=1 width=43)
                ->  Seq Scan on categories  (cost=0.00..1.62 rows=1 
width=43)

EXPLAIN

Here is the after:


fp2migration=# explain
fp2migration-# UPDATE Ports_Check SET found_in_ports = TRUE
fp2migration-#         WHERE EXISTS
fp2migration-# ( SELECT *
fp2migration(#                   FROM categories, element, ports, 
Ports_Check
fp2migration(#                  WHERE ports.category_id = categories.id
fp2migration(#                    AND ports.element_id  = element.id
fp2migration(#                    AND categories.name   = 
Ports_Check.category_name
fp2migration(#                    AND element.name      = 
Ports_Check.port_name);
NOTICE:  QUERY PLAN:

Result  (cost=0.00..119.09 rows=6709 width=27)
  InitPlan
    ->  Hash Join  (cost=135.86..39106.26 rows=207 width=1095)
          ->  Nested Loop  (cost=0.00..38403.39 rows=6862 width=1073)
                ->  Nested Loop  (cost=0.00..15811.86 rows=6862 
width=1043)
                      ->  Seq Scan on ports  (cost=0.00..938.99 rows=6999 
width=1000)
                      ->  Seq Scan on categories  (cost=0.00..1.50 rows=50 
width=43)
                ->  Index Scan using element_pkey on element  
(cost=0.00..3.28 rows=1 width=30)
          ->  Hash  (cost=119.09..119.09 rows=6709 width=22)
                ->  Seq Scan on ports_check  (cost=0.00..119.09 rows=6709 
width=22)
  ->  Seq Scan on ports_check  (cost=0.00..119.09 rows=6709 width=27)

EXPLAIN


The difference between the two SQL statements is the inclusion of 
Ports_Check in the WHERE EXISTS select clause.

Would you expect that?

FWIW: I've done a "vacuum full analyze".
-- 
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: why the big difference on this explain analyze?
  2002-03-16 22:07 why the big difference on this explain analyze? Dan Langille <dan@langille.org>
@ 2002-03-16 22:19 ` Tom Lane <tgl@sss.pgh.pa.us>
  2 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2002-03-16 22:19 UTC (permalink / raw)
  To: dan@langille.org; +Cc: pgsql-sql

"Dan Langille" <dan@langille.org> writes:
> The difference between the two SQL statements is the inclusion of 
> Ports_Check in the WHERE EXISTS select clause.

Which caused it to do the wrong thing.  I think you'll find this
command set found_in_ports = TRUE in *every* row of Ports_Check.

Reason: the EXISTS clause no longer depends on the outer update,
but evaluates to a constant TRUE.  (Or possibly a constant FALSE,
but that seems unlikely.)

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: why the big difference on this explain analyze?
  2002-03-16 22:07 why the big difference on this explain analyze? Dan Langille <dan@langille.org>
@ 2002-03-16 22:22 ` Dan Langille <dan@langille.org>
  2 siblings, 0 replies; 4+ messages in thread

From: Dan Langille @ 2002-03-16 22:22 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

On 16 Mar 2002 at 17:19, Tom Lane wrote:

> "Dan Langille" <dan@langille.org> writes:
> > The difference between the two SQL statements is the inclusion of 
> > Ports_Check in the WHERE EXISTS select clause.
> 
> Which caused it to do the wrong thing.  I think you'll find this
> command set found_in_ports = TRUE in *every* row of Ports_Check.

Yes, it did.  Not quite what I want either....

> Reason: the EXISTS clause no longer depends on the outer update,
> but evaluates to a constant TRUE.  (Or possibly a constant FALSE,
> but that seems unlikely.)

Seems I'll have to keep searching for my speed improvement.  Thanks for 
pointing out that problem.  cheers.
-- 
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: why the big difference on this explain analyze?
  2002-03-16 22:07 why the big difference on this explain analyze? Dan Langille <dan@langille.org>
@ 2002-03-17 00:18 ` Dan Langille <dan@langille.org>
  2 siblings, 0 replies; 4+ messages in thread

From: Dan Langille @ 2002-03-17 00:18 UTC (permalink / raw)
  To: pgsql-sql

On 16 Mar 2002 at 17:07, Dan Langille wrote:

> I was trying to get a query to run.  Yes run.  Not run fast.  Just run.  
> ;) It was taking far too long.
> 
> Here is the before:
> 
> explain 
> fp2migration=#
> fp2migration=# explain
> fp2migration-# UPDATE Ports_Check SET found_in_ports = TRUE
> fp2migration-#         WHERE EXISTS
> fp2migration-#             (   SELECT *
> fp2migration(#                   FROM categories, element, ports
> fp2migration(#                  WHERE ports.category_id = categories.id
> fp2migration(#                    AND ports.element_id  = element.id
> fp2migration(#                    AND categories.name   =
> Ports_Check.category_name fp2migration(#                    AND
> element.name      = Ports_Check.port_name); NOTICE:  QUERY PLAN:
> 
> Seq Scan on ports_check  (cost=0.00..7226574.01 rows=3354 width=27)
>   SubPlan
>     ->  Hash Join  (cost=103.10..1077.13 rows=1 width=1073)
>           ->  Hash Join  (cost=101.47..1075.49 rows=2 width=1030)
>                 ->  Seq Scan on ports  (cost=0.00..938.99 rows=6999 
> width=1000)
>                 ->  Hash  (cost=101.40..101.40 rows=27 width=30)
>                       ->  Index Scan using element_name on element  
> (cost=0.00..101.40 rows=27 width=30)
>           ->  Hash  (cost=1.62..1.62 rows=1 width=43)
>                 ->  Seq Scan on categories  (cost=0.00..1.62 rows=1 
> width=43)

FWIW, I solved the high cost by adding an index:

create index ports_element_id on ports (element_id);

The plan is now:

fp2migration-# UPDATE Ports_Check SET found_in_ports = TRUE
fp2migration-#         WHERE EXISTS
fp2migration-#             (   SELECT *
fp2migration(#                   FROM categories, element, ports
fp2migration(#                  WHERE ports.category_id = categories.id
fp2migration(#                    AND ports.element_id  = element.id
fp2migration(#                    AND categories.name   = 
Ports_Check.category_name
fp2migration(#                    AND element.name      = 
Ports_Check.port_name);
NOTICE:  QUERY PLAN:

Seq Scan on ports_check  (cost=0.00..1772151.04 rows=3354 width=39)
  SubPlan
    ->  Hash Join  (cost=1.63..264.13 rows=1 width=1060)
          ->  Nested Loop  (cost=0.00..262.49 rows=2 width=1017)
                ->  Index Scan using element_name on element  
(cost=0.00..100.78 rows=27 width=30)
                ->  Index Scan using ports_element_id on ports  
(cost=0.00..6.01 rows=1 width=987)
          ->  Hash  (cost=1.62..1.62 rows=1 width=43)
                ->  Seq Scan on categories  (cost=0.00..1.62 rows=1 
width=43)

EXPLAIN

Which takes about 7 seconds to run.  Thank you.
-- 
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2002-03-17 00:18 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-16 22:07 why the big difference on this explain analyze? Dan Langille <dan@langille.org>
2002-03-16 22:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-03-16 22:22 ` Dan Langille <dan@langille.org>
2002-03-17 00:18 ` Dan Langille <dan@langille.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox