From: Tomasz Myrta <jasiek@klaster.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Lista dyskusyjna pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: explicit joins wrong planning
Date: Fri, 28 Nov 2003 21:13:35 +0100
Message-ID: <3FC7AC6F.7080603@klaster.net> (raw)
In-Reply-To: <296.1070049123@sss.pgh.pa.us>
References: <3FC63057.3080009@klaster.net>
<29784.1070047126@sss.pgh.pa.us>
<3FC7A565.4000104@klaster.net>
<296.1070049123@sss.pgh.pa.us>
Dnia 2003-11-28 20:52, Użytkownik Tom Lane napisał:
> Tomasz Myrta <jasiek@klaster.net> writes:
>
>>If I add "and k.id_trasy=lt.id_trasy" into where clause (duplicate), the
>>query works fine.
>
>
> Define "works fine", please (again, EXPLAIN ANALYZE would be a nice
> concrete description).
Sorry, I thought the wrong case would be enough.
In first case (the worse one) I had:
Total runtime: 678.31 msec
After my changes I got:
explain analyze select *
from plany pl
join linia_trasy lt using (id_linii)
join kursy k on (k.event_date=pl.begindate+lt.offset and
k.id_trasy=lt.id_trasy)
where pl.id_planu=508
and k.id_trasy=lt.id_trasy;
^^^^^^^^^^^^^^^^^^^^^^^^^^^
QUERY PLAN
--------------------------------------------------------
Nested Loop (cost=0.00..94.43 rows=2 width=40) (actual
time=2.97..77.55 rows=5 loops=1)
Join Filter: ("inner".event_date = ("outer".begindate + "outer".offset))
-> Nested Loop (cost=0.00..5.81 rows=7 width=29) (actual
time=0.15..0.41 rows=7 loops=1)
Join Filter: ("outer".id_linii = "inner".id_linii)
-> Index Scan using plany_pkey on plany pl (cost=0.00..4.49
rows=1 width=17) (actual time=0.09..0.10 rows=1 loops=1)
Index Cond: (id_planu = 508)
-> Seq Scan on linia_trasy lt (cost=0.00..1.14 rows=14
width=12) (actual time=0.02..0.15 rows=14 loops=1)
-> Index Scan using kursy_pkey on kursy k (cost=0.00..7.62
rows=288 width=11) (actual time=0.05..8.01 rows=533 loops=7)
Index Cond: ((k.id_trasy = "outer".id_trasy) AND (k.id_trasy =
"outer".id_trasy))
Total runtime: 78.01 msec
It's much better now (10x faster), but I've just found this plan still
isn't as I want to have. I wish I could have index usage on both fields,
it means:
Index Cond: ((k.id_trasy = "outer".id_trasy) AND (("inner".event_date =
("outer".begindate + "outer".offset)
Regards,
Tomasz Myrta
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: jasiek@klaster.net, tgl@sss.pgh.pa.us
Subject: Re: explicit joins wrong planning
In-Reply-To: <3FC7AC6F.7080603@klaster.net>
* 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