pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dan Langille <dan@langille.org>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: why the big difference on this explain analyze?
Date: Sat, 16 Mar 2002 19:18:11 -0500
Message-ID: <20020317001814.27A883F28@bast.unixathome.org> (raw)
In-Reply-To: <20020316220751.A5EE73F28@bast.unixathome.org>

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




view thread (4+ messages)

Message-ID: <20020317001814.27A883F28@bast.unixathome.org>
Permalink:  ../20020317001814.27A883F28@bast.unixathome.org/
Also on:    postgresql.org/message-id/20020317001814.27A883F28@bast.unixathome.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: dan@langille.org
  Subject: Re: why the big difference on this explain analyze?
  In-Reply-To: <20020317001814.27A883F28@bast.unixathome.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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