Skip site navigation (1) Skip section navigation (2)

OFFSET impact on Performance???

From: "Andrei Bintintan" <klodoma(at)ar-sd(dot)net>
To: <pgsql-sql(at)postgresql(dot)org>, <pgsql-performance(at)postgresql(dot)org>
Subject: OFFSET impact on Performance???
Date: 2005-01-20 11:13:44
Message-ID: 019f01c4fee1$1b58d600$0b00a8c0@forge (view raw or flat)
Thread:
Lists: pgsql-performancepgsql-sql
Hi to all, 

I have the following 2 examples. Now, regarding on the offset if it is small(10) or big(>50000) what is the impact on the performance of the query?? I noticed that if I return more data's(columns) or if I make more joins then the query runs even slower if the OFFSET is bigger. How can I somehow improve the performance on this? 

Best regards, 
Andy.

explain analyze
SELECT o.id
FROM report r 
INNER JOIN orders o ON o.id=r.id_order AND o.id_status=6
ORDER BY 1 LIMIT 10 OFFSET 10


Limit  (cost=44.37..88.75 rows=10 width=4) (actual time=0.160..0.275 rows=10 loops=1)
  ->  Merge Join  (cost=0.00..182150.17 rows=41049 width=4) (actual time=0.041..0.260 rows=20 loops=1)
        Merge Cond: ("outer".id_order = "inner".id)
        ->  Index Scan using report_id_order_idx on report r  (cost=0.00..157550.90 rows=42862 width=4) (actual time=0.018..0.075 rows=20 loops=1)
        ->  Index Scan using orders_pkey on orders o  (cost=0.00..24127.04 rows=42501 width=4) (actual time=0.013..0.078 rows=20 loops=1)
              Filter: (id_status = 6)
Total runtime: 0.373 ms

explain analyze
SELECT o.id
FROM report r 
INNER JOIN orders o ON o.id=r.id_order AND o.id_status=6
ORDER BY 1 LIMIT 10 OFFSET 1000000

Limit  (cost=31216.85..31216.85 rows=1 width=4) (actual time=1168.152..1168.152 rows=0 loops=1)
  ->  Sort  (cost=31114.23..31216.85 rows=41049 width=4) (actual time=1121.769..1152.246 rows=42693 loops=1)
        Sort Key: o.id
        ->  Hash Join  (cost=2329.99..27684.03 rows=41049 width=4) (actual time=441.879..925.498 rows=42693 loops=1)
              Hash Cond: ("outer".id_order = "inner".id)
              ->  Seq Scan on report r  (cost=0.00..23860.62 rows=42862 width=4) (actual time=38.634..366.035 rows=42864 loops=1)
              ->  Hash  (cost=2077.74..2077.74 rows=42501 width=4) (actual time=140.200..140.200 rows=0 loops=1)
                    ->  Seq Scan on orders o  (cost=0.00..2077.74 rows=42501 width=4) (actual time=0.059..96.890 rows=42693 loops=1)
                          Filter: (id_status = 6)
Total runtime: 1170.586 ms

Responses

pgsql-performance by date

Next:From: Dan LangilleDate: 2005-01-20 11:56:20
Subject: Re: index scan of whole table, can't see why
Previous:From: Bernd HellerDate: 2005-01-20 10:14:28
Subject: column without pg_stats entry?!

pgsql-sql by date

Next:From: Richard HuxtonDate: 2005-01-20 12:10:59
Subject: Re: [SQL] OFFSET impact on Performance???
Previous:From: Mihail NasedkinDate: 2005-01-20 10:45:58
Subject: OID's

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group