Re: speed w/ OFFSET/LIMIT

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)