From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Antoine Baudoux <ab@taktik.be>
Cc: Guillaume Smet <guillaume.smet@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: multiple joins + Order by + LIMIT query performance issue
Date: Tue, 06 May 2008 19:06:35 +0100
Message-ID: <48209E2B.90007@enterprisedb.com> (raw)
In-Reply-To: <A86234CE-E5E7-4157-9F1F-7B574872F6D6@taktik.be>
References: <938EC908-FB9A-4BFA-950A-053BE3C87085@taktik.be>
<1d4e0c10805060838i5927b15bm26f9d9e2dac23520@mail.gmail.com>
<A86234CE-E5E7-4157-9F1F-7B574872F6D6@taktik.be>
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
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-performance@postgresql.org
Cc: heikki@enterprisedb.com, ab@taktik.be, guillaume.smet@gmail.com
Subject: Re: multiple joins + Order by + LIMIT query performance issue
In-Reply-To: <48209E2B.90007@enterprisedb.com>
* 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