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
