X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id C31EED1B493 for ; Fri, 28 Nov 2003 20:14:25 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 92444-02 for ; Fri, 28 Nov 2003 16:13:56 -0400 (AST) Received: from serwer.skawsoft.com.pl (skawina.eu.org [80.48.213.66]) by svr1.postgresql.org (Postfix) with ESMTP id 64D68D1B472 for ; Fri, 28 Nov 2003 16:13:54 -0400 (AST) Received: from klaster.net (core-1.citynet.pl [80.48.135.69]) by serwer.skawsoft.com.pl (Postfix) with ESMTP id 3B9862B3D5; Fri, 28 Nov 2003 21:13:30 +0100 (CET) Message-ID: <3FC7AC6F.7080603@klaster.net> Date: Fri, 28 Nov 2003 21:13:35 +0100 From: Tomasz Myrta User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; pl-PL; rv:1.5) Gecko/20031007 X-Accept-Language: pl, en-us, en MIME-Version: 1.0 To: Tom Lane Cc: Lista dyskusyjna pgsql-sql Subject: Re: explicit joins wrong planning References: <3FC63057.3080009@klaster.net> <29784.1070047126@sss.pgh.pa.us> <3FC7A565.4000104@klaster.net> <296.1070049123@sss.pgh.pa.us> In-Reply-To: <296.1070049123@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-2; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/330 X-Sequence-Number: 16145 Dnia 2003-11-28 20:52, Użytkownik Tom Lane napisał: > Tomasz Myrta 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