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)