why the big difference on this explain analyze? - Mailing list 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=1width=1073)         ->  Hash Join  (cost=101.47..1075.49 rows=2 width=1030)               ->  Seq Scan on ports
(cost=0.00..938.99rows=6999 
 
width=1000)               ->  Hash  (cost=101.40..101.40 rows=27 width=30)                     ->  Index Scan using
element_nameon 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
onports_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