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 182263A5933 for ; Thu, 27 Jan 2005 03:50:55 +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 81472-07 for ; Thu, 27 Jan 2005 03:50:41 +0000 (GMT) Received: from gizmo01bw.bigpond.com (gizmo01bw.bigpond.com [144.140.70.11]) by svr1.postgresql.org (Postfix) with SMTP id 893773A58F8 for ; Thu, 27 Jan 2005 03:50:40 +0000 (GMT) Received: (qmail 27048 invoked from network); 27 Jan 2005 03:50:37 -0000 Received: from unknown (HELO bwmam09.bigpond.com) (144.135.24.94) by gizmo01bw.bigpond.com with SMTP; 27 Jan 2005 03:50:37 -0000 Received: from cpe-203-45-184-181.qld.bigpond.net.au ([203.45.184.181]) by bwmam09.bigpond.com(MAM REL_3_4_2a 144/26733581) with SMTP id 26733581; Thu, 27 Jan 2005 13:50:37 +1000 Message-ID: <41F86501.1060006@bigpond.net.au> Date: Thu, 27 Jan 2005 13:50:25 +1000 From: David Brown User-Agent: Mozilla Thunderbird 1.0RC1 (Windows/20041201) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Andrei Bintintan Subject: Re: OFFSET impact on Performance??? References: <019f01c4fee1$1b58d600$0b00a8c0@forge> In-Reply-To: <019f01c4fee1$1b58d600$0b00a8c0@forge> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.676 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_WHOIS X-Spam-Level: * X-Archive-Number: 200501/522 X-Sequence-Number: 10212 Although larger offsets have some effect, your real problem is the sort (of 42693 rows). Try: SELECT r.id_order FROM report r WHERE r.id_order IN (SELECT id FROM orders WHERE id_status = 6 ORDER BY 1 LIMIT 10 OFFSET 1000) ORDER BY 1 The subquery doesn't *have* to sort because the table is already ordered on the primary key. You can still add a join to orders outside the subselect without significant cost. Incidentally, I don't know how you got the first plan - it should include a sort as well. Andrei Bintintan wrote: > 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