X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 81C483A4334; Thu, 20 Jan 2005 11:13:46 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 78962-07; Thu, 20 Jan 2005 11:13:38 +0000 (GMT) Received: from ar-sd.net (unknown [82.77.155.72]) by svr1.postgresql.org (Postfix) with ESMTP id DBA363A4263; Thu, 20 Jan 2005 11:13:38 +0000 (GMT) Received: from localhost (localhost [127.0.0.1]) by ar-sd.net (Postfix) with ESMTP id C0C9E21308; Thu, 20 Jan 2005 13:09:09 +0200 (EET) Received: from ar-sd.net ([127.0.0.1]) by localhost (linz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 03939-04; Thu, 20 Jan 2005 13:09:08 +0200 (EET) Received: from forge (unknown [192.168.0.11]) by ar-sd.net (Postfix) with SMTP id 2A205212F0; Thu, 20 Jan 2005 13:09:08 +0200 (EET) Message-ID: <019f01c4fee1$1b58d600$0b00a8c0@forge> From: "Andrei Bintintan" To: , Subject: OFFSET impact on Performance??? Date: Thu, 20 Jan 2005 13:13:44 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_019A_01C4FEF1.DE1BBDF0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at ar-sd.net X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.06 tagged_above=0 required=5 tests=AWL, HTML_50_60, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200501/262 X-Sequence-Number: 9952 This is a multi-part message in MIME format. ------=_NextPart_000_019A_01C4FEF1.DE1BBDF0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi to all,=20 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?=20 Best regards,=20 Andy. explain analyze SELECT o.id FROM report r=20 INNER JOIN orders o ON o.id=3Dr.id_order AND o.id_status=3D6 ORDER BY 1 LIMIT 10 OFFSET 10 Limit (cost=3D44.37..88.75 rows=3D10 width=3D4) (actual = time=3D0.160..0.275 rows=3D10 loops=3D1) -> Merge Join (cost=3D0.00..182150.17 rows=3D41049 width=3D4) = (actual time=3D0.041..0.260 rows=3D20 loops=3D1) Merge Cond: ("outer".id_order =3D "inner".id) -> Index Scan using report_id_order_idx on report r = (cost=3D0.00..157550.90 rows=3D42862 width=3D4) (actual = time=3D0.018..0.075 rows=3D20 loops=3D1) -> Index Scan using orders_pkey on orders o = (cost=3D0.00..24127.04 rows=3D42501 width=3D4) (actual = time=3D0.013..0.078 rows=3D20 loops=3D1) Filter: (id_status =3D 6) Total runtime: 0.373 ms explain analyze SELECT o.id FROM report r=20 INNER JOIN orders o ON o.id=3Dr.id_order AND o.id_status=3D6 ORDER BY 1 LIMIT 10 OFFSET 1000000 Limit (cost=3D31216.85..31216.85 rows=3D1 width=3D4) (actual = time=3D1168.152..1168.152 rows=3D0 loops=3D1) -> Sort (cost=3D31114.23..31216.85 rows=3D41049 width=3D4) (actual = time=3D1121.769..1152.246 rows=3D42693 loops=3D1) Sort Key: o.id -> Hash Join (cost=3D2329.99..27684.03 rows=3D41049 width=3D4) = (actual time=3D441.879..925.498 rows=3D42693 loops=3D1) Hash Cond: ("outer".id_order =3D "inner".id) -> Seq Scan on report r (cost=3D0.00..23860.62 = rows=3D42862 width=3D4) (actual time=3D38.634..366.035 rows=3D42864 = loops=3D1) -> Hash (cost=3D2077.74..2077.74 rows=3D42501 width=3D4) = (actual time=3D140.200..140.200 rows=3D0 loops=3D1) -> Seq Scan on orders o (cost=3D0.00..2077.74 = rows=3D42501 width=3D4) (actual time=3D0.059..96.890 rows=3D42693 = loops=3D1) Filter: (id_status =3D 6) Total runtime: 1170.586 ms ------=_NextPart_000_019A_01C4FEF1.DE1BBDF0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi to all,

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

Best regards,
Andy.

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

 
Limit  (cost=3D44.37..88.75 = rows=3D10 width=3D4)=20 (actual time=3D0.160..0.275 rows=3D10 loops=3D1)
  ->  = Merge=20 Join  (cost=3D0.00..182150.17 rows=3D41049 width=3D4) (actual = time=3D0.041..0.260=20 rows=3D20 loops=3D1)
        Merge = Cond:=20 ("outer".id_order =3D = "inner".id)
       =20 ->  Index Scan using report_id_order_idx on report r =20 (cost=3D0.00..157550.90 rows=3D42862 width=3D4) (actual = time=3D0.018..0.075 rows=3D20=20 loops=3D1)
        ->  = Index Scan=20 using orders_pkey on orders o  (cost=3D0.00..24127.04 rows=3D42501 = width=3D4)=20 (actual time=3D0.013..0.078 rows=3D20=20 loops=3D1)
          = ;   =20 Filter: (id_status =3D 6)
Total runtime: 0.373 ms

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

Limit  (cost=3D31216.85..31216.85 rows=3D1 width=3D4) = (actual=20 time=3D1168.152..1168.152 rows=3D0 loops=3D1)
  ->  = Sort =20 (cost=3D31114.23..31216.85 rows=3D41049 width=3D4) (actual = time=3D1121.769..1152.246=20 rows=3D42693 loops=3D1)
        = Sort Key:=20 o.id
        ->  Hash = Join =20 (cost=3D2329.99..27684.03 rows=3D41049 width=3D4) (actual = time=3D441.879..925.498=20 rows=3D42693=20 loops=3D1)
          = ;   =20 Hash Cond: ("outer".id_order =3D=20 "inner".id)
         &nbs= p;   =20 ->  Seq Scan on report r  (cost=3D0.00..23860.62 = rows=3D42862 width=3D4)=20 (actual time=3D38.634..366.035 rows=3D42864=20 loops=3D1)
          = ;   =20 ->  Hash  (cost=3D2077.74..2077.74 rows=3D42501 width=3D4) = (actual=20 time=3D140.200..140.200 rows=3D0=20 loops=3D1)
          = ;         =20 ->  Seq Scan on orders o  (cost=3D0.00..2077.74 = rows=3D42501 width=3D4)=20 (actual time=3D0.059..96.890 rows=3D42693=20 loops=3D1)
          = ;            =    =20 Filter: (id_status =3D 6)
Total runtime: 1170.586=20 ms
------=_NextPart_000_019A_01C4FEF1.DE1BBDF0--