Re: multiple joins + Order by + LIMIT query performance issue - Mailing list pgsql-performance
From | Antoine Baudoux |
---|---|
Subject | Re: multiple joins + Order by + LIMIT query performance issue |
Date | |
Msg-id | A86234CE-E5E7-4157-9F1F-7B574872F6D6@taktik.be Whole thread Raw |
In response to | Re: multiple joins + Order by + LIMIT query performance issue ("Guillaume Smet" <guillaume.smet@gmail.com>) |
Responses |
Re: multiple joins + Order by + LIMIT query performance issue
("Heikki Linnakangas" <heikki@enterprisedb.com>)
Re: multiple joins + Order by + LIMIT query performance issue ("Heikki Linnakangas" <heikki@enterprisedb.com>) |
List | pgsql-performance |
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 On May 6, 2008, at 5:38 PM, Guillaume Smet wrote: > Antoine, > > On Tue, May 6, 2008 at 5:03 PM, Antoine Baudoux <ab@taktik.be> wrote: >> "Limit (cost=23981.18..23981.18 rows=1 width=977)" >> " -> Sort (cost=23981.18..23981.18 rows=1 width=977)" >> " Sort Key: this_.c_date" > > Can you please provide the EXPLAIN ANALYZE output instead of EXPLAIN? > > Thanks. > > -- > Guillaume
pgsql-performance by date: