Re: speed w/ OFFSET/LIMIT
От | Damien |
---|---|
Тема | Re: speed w/ OFFSET/LIMIT |
Дата | |
Msg-id | 200305271824.40650.dm_mailings@abelia-decors.com обсуждение исходный текст |
Ответ на | Re: speed w/ OFFSET/LIMIT (Stephan Szabo <sszabo@megazone23.bigpanda.com>) |
Ответы |
Re: speed w/ OFFSET/LIMIT
(Stephan Szabo <sszabo@megazone23.bigpanda.com>)
|
Список | pgsql-general |
On Tuesday 27 May 2003 17:47, Stephan Szabo wrote: > Can you send exact query and explain analyze output for each? Since it > has to get the x+50 I'm not sure what can be done, but the explain output > will help. > > As a side note, the workaround in your following message works as long as > the joins give only one match, but won't if they don't (the results are > different in that case). Here is the output. As you can see the explainations really differs depending of the given offset : optima=# EXPLAIN SELECT a.* , p.palette , e.etat_detail , s.status_detail optima-# FROM da4adresse a optima-# LEFT OUTER JOIN da4paletier p ON p.adresse = a.adresse optima-# JOIN da4status s ON s.status = a.status optima-# JOIN da4etat e ON e.etat = a.etat optima-# ORDER BY a.adresse LIMIT 50 OFFSET 1500 ; QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Limit (cost=4022.58..4156.63 rows=50 width=154) -> Nested Loop (cost=1.05..194138.88 rows=72412 width=154) Join Filter: ("inner".etat = "outer".etat) -> Nested Loop (cost=1.05..115209.80 rows=72412 width=139) Join Filter: ("inner".status = "outer".status) -> Merge Join (cost=1.05..37909.99 rows=72412 width=124) Merge Cond: ("outer".adresse = "inner".adresse) -> Index Scan using pk_adresse on da4adresse a (cost=0.00..41296.38 rows=72412 width=106) -> Sort (cost=1.05..1.06 rows=3 width=18) Sort Key: p.adresse -> Seq Scan on da4paletier p (cost=0.00..1.03 rows=3 width=18) -> Seq Scan on da4status s (cost=0.00..1.03 rows=3 width=15) -> Seq Scan on da4etat e (cost=0.00..1.04 rows=4 width=15) (13 rows) optima=# EXPLAIN SELECT a.* , p.palette , e.etat_detail , s.status_detail optima-# FROM da4adresse a optima-# LEFT OUTER JOIN da4paletier p ON p.adresse = a.adresse optima-# JOIN da4status s ON s.status = a.status optima-# JOIN da4etat e ON e.etat = a.etat optima-# ORDER BY a.adresse LIMIT 50 OFFSET 70000 ; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Limit (cost=28336.02..28336.15 rows=50 width=154) -> Sort (cost=28161.02..28342.05 rows=72412 width=154) Sort Key: a.adresse -> Merge Join (cost=21048.72..22315.95 rows=72412 width=154) Merge Cond: ("outer".etat = "inner".etat) -> Sort (cost=1.08..1.09 rows=4 width=15) Sort Key: e.etat -> Seq Scan on da4etat e (cost=0.00..1.04 rows=4 width=15) -> Sort (cost=21047.64..21228.67 rows=72412 width=139) Sort Key: a.etat -> Merge Join (cost=13935.34..15202.57 rows=72412 width=139) Merge Cond: ("outer".status = "inner".status) -> Sort (cost=13934.29..14115.32 rows=72412 width=124) Sort Key: a.status -> Merge Join (cost=7758.25..8089.21 rows=72412 width=124) Merge Cond: ("outer".adresse = "inner".adresse) -> Sort (cost=7757.20..7938.23 rows=72412 width=106) Sort Key: a.adresse -> Seq Scan on da4adresse a (cost=0.00..1912.12 rows=72412 width=106) -> Sort (cost=1.05..1.06 rows=3 width=18) Sort Key: p.adresse -> Seq Scan on da4paletier p (cost=0.00..1.03 rows=3 width=18) -> Sort (cost=1.05..1.06 rows=3 width=15) Sort Key: s.status -> Seq Scan on da4status s (cost=0.00..1.03 rows=3 width=15) (25 rows)
В списке pgsql-general по дате отправления: