pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mendola Gaetano <mendola@bigfoot.com>
To: pgsql-admin@postgresql.org
Cc: \"HansH\" <hartenhans@op.het.net>
Subject: Re: Wrong plan or what ?
Date: Fri, 1 Aug 2003 17:49:11 +0200
Message-ID: <006b01c35844$739ec030$32add6c2@mm.eutelsat.org> (raw)
References: <00c101c35074$1e5f5b20$152aa8c0@GMENDOLA2>
	<bftml3$vmq$1@news.cistron.nl>

""HansH"" <hartenhans@op.het.net>
> In response to "Mendola Gaetano":
> > I'm running Postgres7.3.3 and I'm performing this simple select:
>
> Looking at your fast three step plan
> > SELECT id_class from class_default where id_provider = 39;
> > SELECT id_user from user_data where id_class in ( 48 );
> > SELECT * from user_logs where id_user in (
> >  10943,   10942,   10934,   10927,   10910,  10909 );
> I'ld stem for reordering the from and where clauses alike:
>     select *
>     from
>            class_default cd,
>            user_data ud,
>            user_logs ul
>     where
>            cd.id_provider = 39 and
>            ud.id_class = cd.id_class and
>            ul.id_user = ud.id_user;


still wrong:

Hash Join  (cost=267.10..32994.34 rows=41881 width=264) (actual
time=6620.17..6847.20 rows=94 loops=1)
   Hash Cond: ("outer".id_user = "inner".id_user)
   ->  Seq Scan on user_logs ul  (cost=0.00..25712.15 rows=1298315 width=48)
(actual time=0.01..5381.69 rows=1298351 loops=1)
   ->  Hash  (cost=266.25..266.25 rows=339 width=216) (actual
time=0.89..0.89 rows=0 loops=1)
         ->  Nested Loop  (cost=0.00..266.25 rows=339 width=216) (actual
time=0.16..0.83 rows=21 loops=1)
               ->  Seq Scan on class_default cd  (cost=0.00..1.39 rows=1
width=55) (actual time=0.08..0.09 rows=1 loops=1)
                     Filter: (id_provider = 39)
               ->  Index Scan using idx_user_data_class on user_data ud
(cost=0.00..260.00 rows=389 width=161) (actual time=0.06..0.40 rows=21
loops=1)
                     Index Cond: (ud.id_class = "outer".id_class)
 Total runtime: 6847.60 msec
(10 rows)


the returned are 94.


> Personally I dislike implied joins and rather go for _about_ this:
>     select *
>     from
>            ( class_default cd
>            LEFT JOIN user_data ud ON ud.id_class = cd.id_class )
>            LEFT JOIN user_logs ul  ON ul.id_user = ud.id_user,
>     where
>            cd.id_provider = 39;

worst:

 Merge Join  (cost=280.48..55717.14 rows=41881 width=264) (actual
time=18113.64..18182.94 rows=105 loops=1)
   Merge Cond: ("outer".id_user = "inner".id_user)
   ->  Index Scan using idx_user_user_logs on user_logs ul
(cost=0.00..51665.66 rows=1298315 width=48) (actual time=10.78..15459.37
rows=1298354 loops=1)
   ->  Sort  (cost=280.48..281.33 rows=339 width=216) (actual
time=1.11..1.20 rows=105 loops=1)
         Sort Key: ud.id_user
         ->  Nested Loop  (cost=0.00..266.25 rows=339 width=216) (actual
time=0.14..0.82 rows=21 loops=1)
               ->  Seq Scan on class_default cd  (cost=0.00..1.39 rows=1
width=55) (actual time=0.07..0.07 rows=1 loops=1)
                     Filter: (id_provider = 39)
               ->  Index Scan using idx_user_data_class on user_data ud
(cost=0.00..260.00 rows=389 width=161) (actual time=0.05..0.39 rows=21
loops=1)
                     Index Cond: (ud.id_class = "outer".id_class)
 Total runtime: 18185.61 msec

:-(



thank you anyway.

Gaetano





view thread (8+ messages)

Message-ID: <006b01c35844$739ec030$32add6c2@mm.eutelsat.org>
Permalink:  ../006b01c35844$739ec030$32add6c2@mm.eutelsat.org/
Also on:    postgresql.org/message-id/006b01c35844$739ec030$32add6c2@mm.eutelsat.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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: mendola@bigfoot.com, pgsql-admin@postgresql.org, hartenhans@op.het.net
  Subject: Re: Wrong plan or what ?
  In-Reply-To: <006b01c35844$739ec030$32add6c2@mm.eutelsat.org>
  References: <00c101c35074$1e5f5b20$152aa8c0@GMENDOLA2> <bftml3$vmq$1@news.cistron.nl> <006b01c35844$739ec030$32add6c2@mm.eutelsat.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