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
copy link · copy postgr.es
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