thread-index: AcivpBxPcVk4ysU6QHCKyxj1vMUs6Q==
Content-Class: urn:content-classes:message
Importance: normal
Message-ID: <48209E2B.90007@enterprisedb.com>
Date: Tue, 06 May 2008 19:06:35 +0100
From: "Heikki Linnakangas" <heikki@enterprisedb.com>
Organization: EnterpriseDB
User-Agent: Mozilla-Thunderbird 2.0.0.9 (X11/20080110)
MIME-Version: 1.0
To: "Antoine Baudoux" <ab@taktik.be>
Cc: "Guillaume Smet" <guillaume.smet@gmail.com>,
	<pgsql-performance@postgresql.org>
Subject: Re: multiple joins + Order by + LIMIT query performance issue
References: <938EC908-FB9A-4BFA-950A-053BE3C87085@taktik.be>
	<1d4e0c10805060838i5927b15bm26f9d9e2dac23520@mail.gmail.com>
	<A86234CE-E5E7-4157-9F1F-7B574872F6D6@taktik.be>
In-Reply-To: <A86234CE-E5E7-4157-9F1F-7B574872F6D6@taktik.be>
Content-Type: text/plain;
	format=flowed;
	charset="ISO-8859-1"
Content-Transfer-Encoding: 7bit

Antoine Baudoux wrote:
> Here is the explain analyse for the first query, the other is still 
> running...
> 
> 
> explain analyse select * from t_Event event
> inner join t_Service service on event.service_id=service.id
> inner join t_System system on service.system_id=system.id
> inner join t_Interface interface on system.id=interface.system_id
> inner join t_Network network on interface.network_id=network.id
> where (network.customer_id=1) order by event.c_date desc limit 25
> 
> Limit  (cost=11761.44..11761.45 rows=1 width=976) (actual 
> time=0.047..0.047 rows=0 loops=1)
>   ->  Sort  (cost=11761.44..11761.45 rows=1 width=976) (actual 
> time=0.045..0.045 rows=0 loops=1)
>         Sort Key: event.c_date
>         Sort Method:  quicksort  Memory: 17kB
>         ->  Nested Loop  (cost=0.00..11761.43 rows=1 width=976) (actual 
> time=0.024..0.024 rows=0 loops=1)
>               ->  Nested Loop  (cost=0.00..11755.15 rows=1 width=960) 
> (actual time=0.024..0.024 rows=0 loops=1)
>                     ->  Nested Loop  (cost=0.00..191.42 rows=1 
> width=616) (actual time=0.024..0.024 rows=0 loops=1)
>                           Join Filter: (interface.system_id = 
> service.system_id)
>                           ->  Nested Loop  (cost=0.00..9.29 rows=1 
> width=576) (actual time=0.023..0.023 rows=0 loops=1)
>                                 ->  Seq Scan on t_network network  
> (cost=0.00..1.01 rows=1 width=18) (actual time=0.009..0.009 rows=1 loops=1)
>                                       Filter: (customer_id = 1)
>                                 ->  Index Scan using 
> interface_network_id_idx on t_interface interface  (cost=0.00..8.27 
> rows=1 width=558) (actual time=0.011..0.011 rows=0 loops=1)
>                                       Index Cond: (interface.network_id 
> = network.id)
>                           ->  Seq Scan on t_service service  
> (cost=0.00..109.28 rows=5828 width=40) (never executed)
>                     ->  Index Scan using event_svc_id_idx on t_event 
> event  (cost=0.00..11516.48 rows=3780 width=344) (never executed)
>                           Index Cond: (event.service_id = service.id)
>               ->  Index Scan using t_system_pkey on t_system system  
> (cost=0.00..6.27 rows=1 width=16) (never executed)
>                     Index Cond: (system.id = service.system_id)
> Total runtime: 0.362 ms

Are the queries returning the same results (except for the extra columns 
coming from t_network)? It looks like in this version, the 
network-interface join is performed first, which returns zero rows, so 
the rest of the joins don't need to be performed at all. That's why it's 
fast.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com