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:04:17 +0100
Message-ID: <48209DA1.9050800@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 even 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.
Which version of PostgreSQL is this, BTW?
--
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: <48209DA1.9050800@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