Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id BF47A9FA3A8 for ; Tue, 24 Jul 2007 05:12:05 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 64439-07 for ; Tue, 24 Jul 2007 05:11:59 -0300 (ADT) X-Greylist: delayed 00:23:11.832064 by SQLgrey-1.7.5 Received: from bk0.streamy.com (ns1.streamy.com [72.34.242.2]) by postgresql.org (Postfix) with ESMTP id 8900F9FA31A for ; Tue, 24 Jul 2007 05:11:58 -0300 (ADT) Received: from maintux ([71.189.126.93]) by bk0.streamy.com (8.13.1/8.13.1) with ESMTP id l6O7ma6Y008383; Tue, 24 Jul 2007 00:48:36 -0700 From: "Jonathan Gray" To: Subject: Query performance issue Date: Tue, 24 Jul 2007 00:48:07 -0700 Organization: Streamy Inc. Message-ID: <0f1701c7cdc6$f9cc1a30$ed644e90$@com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_0F18_01C7CD8C.4D6D4230" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AcfNxvjPe0tfYp+uQBaRuuO4A56Qrw== Content-Language: en-us X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.069 tagged_above=0 required=5 tests=AWL=0.067, BAYES_50=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200707/310 X-Sequence-Number: 25994 This is a multipart message in MIME format. ------=_NextPart_000_0F18_01C7CD8C.4D6D4230 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0F19_01C7CD8C.4D6D4230" ------=_NextPart_001_0F19_01C7CD8C.4D6D4230 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit We're experiencing a query performance problem related to the planner and its ability to perform a specific type of merge. We have created a test case (as attached, or here: http://www3.streamy.com/postgres/indextest.sql) which involves a hypothetical customer ordering system, with customers, orders, and customer groups. If we want to retrieve a single customers 10 most recent orders, sorted by date, we can use a double index on (customer,date); Postgres's query planner will use the double index with a backwards index scan on the second indexed column (date). However, if we want to retrieve a "customer class's" 10 most recent orders, sorted by date, we are not able to get Postgres to use double indexes. We have come to the conclusion that the fastest way to accomplish this type of query is to merge, in sorted order, each customers set of orders (for which we can use the double index). Using a heap to merge these ordered lists (until we reach the limit) seems the most algorithmically efficient way we are able to find. This is implemented in the attachment as a pl/pythonu function. Another less algorithmically efficient solution, but faster in practice for many cases, is to fetch the full limit of orders from each customer, sort these by date, and return up to the limit. We are no masters of reading query plans, but for straight SQL queries the planner seems to yield two different types of plan. They are fast in certain cases but breakdown in our typical use cases, where the number of orders per customer is sparse compared to the total number of orders across the date range. We are interested in whether a mechanism internal to Postgres can accomplish this type of merging of indexed columns in sorted order. If this cannot currently be accomplished (or if there is something we are missing about why it shouldn't be) we would appreciate any pointers to be able to translate our python heap approach into C functions integrated more closely with Postgres. The python function incurs large constant costs because of type conversions and repeated queries to the database. Thanks for any help or direction. Jonathan Gray / Miguel Simon ------=_NextPart_001_0F19_01C7CD8C.4D6D4230 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

We’re experiencing a query performance = problem related to the planner and its ability to perform a specific type of = merge.

 

We have created a test case (as attached, or here: = http://www3.strea= my.com/postgres/indextest.sql) which involves a hypothetical customer ordering system, with customers, = orders, and customer groups.

 

If we want to retrieve a single customers 10 most = recent orders, sorted by date, we can use a double index on (customer,date); = Postgres’s query planner will use the double index with  a backwards index = scan on the second indexed column (date).

 

However, if we want to retrieve a “customer = class’s” 10 most recent orders, sorted by date, we are not able to get Postgres = to use double indexes.

 

We have come to the conclusion that the fastest way = to accomplish this type of query is to merge, in sorted order, each = customers set of orders (for which we can use the double index).  Using a heap to = merge these ordered lists (until we reach the limit) seems the most = algorithmically efficient way we are able to find.  This is implemented in the = attachment as a pl/pythonu function.

 

Another less algorithmically efficient solution, = but faster in practice for many cases, is to fetch the full limit of orders from = each customer, sort these by date, and return up to the limit.

 

We are no masters of reading query plans, but for = straight SQL queries the planner seems to yield two different types of = plan.  They are fast in certain cases but breakdown in our typical use cases, where = the number of orders per customer is sparse compared to the total number of = orders across the date range.

 

We are interested in whether a mechanism internal = to Postgres can accomplish this type of merging of indexed columns in sorted = order.

 

If this cannot currently be accomplished (or if = there is something we are missing about why it shouldn’t be) we would appreciate any pointers to be able to translate our python heap approach into C = functions integrated more closely with Postgres.  The python function incurs = large constant costs because of type conversions and repeated queries to the database.

 

Thanks for any help or direction.

 

Jonathan Gray / Miguel Simon

------=_NextPart_001_0F19_01C7CD8C.4D6D4230-- ------=_NextPart_000_0F18_01C7CD8C.4D6D4230 Content-Type: application/octet-stream; name="indextest.sql" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="indextest.sql" BEGIN; -- Creation CREATE SCHEMA indextest; CREATE TABLE indextest.customers ( customerid SERIAL PRIMARY KEY NOT NULL, customername text NOT NULL ); CREATE TABLE indextest.customerclasses ( classid SERIAL PRIMARY KEY NOT NULL, classname text NOT NULL ); CREATE TABLE indextest.customerclass ( classid INTEGER NOT NULL REFERENCES = indextest.customerclasses(classid), customerid INTEGER NOT NULL REFERENCES indextest.customers(customerid) ); CREATE TABLE indextest.orders ( orderid SERIAL PRIMARY KEY NOT NULL, orderstamp TIMESTAMPTZ NOT NULL DEFAULT now(), customerid INTEGER NOT NULL REFERENCES indextest.customers(customerid) ); CREATE INDEX orders_customerid_orderstamp_idx ON indextest.orders USING = btree(customerid,orderstamp); CREATE INDEX orders_orderstamp_idx ON indextest.orders USING = btree(orderstamp); CREATE INDEX orders_customerid_idx ON indextest.orders USING = btree(customerid); -- Sample Data -- 10k Customers INSERT INTO indextest.customers (customerid,customername) SELECT s, 'Customer ' || s FROM generate_series(0,9999) as s; -- 100 Customer Classes INSERT INTO indextest.customerclasses (classid,classname) SELECT s, 'Customer Class ' || s FROM generate_series(0,99) as s; -- Each Customer into a random Customer Class INSERT INTO indextest.customerclass (classid,customerid) SELECT (random() * 100)::integer % 100, s FROM generate_series(0,9993) = as s; =20 -- Customers 9994 to 9999 go into Customer Classes 0 and 1 INSERT INTO indextest.customerclass (classid,customerid) SELECT s % 2, s FROM generate_series(9994,9999) as s; -- 100k Orders with random Customers INSERT INTO indextest.orders (orderid, orderstamp, customerid) SELECT s, now() - (((random() * 100)::integer || ' hour')::interval) - = (((random() * 100)::integer % 60 || ' minute')::interval), (random() * = 10000)::integer % 10000 FROM generate_series(0,99999) as s; -- 1k Orders (at end of table) spread across Customers 9994 to 9999 = (Customer Classes 0 and 1) INSERT INTO indextest.orders (orderid, orderstamp, customerid) SELECT s, now() - ((s % 1000 + 100 || ' hour')::interval) - = (((random() * 100)::integer % 60 || ' minute')::interval), 9994 + (s % = 6) FROM generate_series(100000,101000) as s; -- 200 Customers at end all in class 5 with hundreds of orders each INSERT INTO indextest.customers (customerid,customername) SELECT s, 'Customer ' || s FROM generate_series(10000,10199) as s; INSERT INTO indextest.customerclass (classid,customerid)=20 SELECT 5, s FROM generate_series(10000,10199) as s; INSERT INTO indextest.orders (orderid,orderstamp,customerid) SELECT s, now() - (((random() * 100)::integer || ' hour')::interval) - = (((random() * 100)::integer % 60 || ' minute')::interval), 10000 + (s % = 200) FROM generate_series(101001,141000) as s; =20 -- Analyze ANALYZE indextest.customers; ANALYZE indextest.customerclasses; ANALYZE indextest.customerclass; ANALYZE indextest.orders; -- Functions to be used CREATE TYPE indextest.order_and_stamp AS ( orderid integer, orderstamp timestamptz ); =20 CREATE OR REPLACE FUNCTION indextest.orders_by_class(classid_in INTEGER, = limit_in INTEGER) RETURNS SETOF indextest.order_and_stamp AS $$ DECLARE customerid_var integer; oas_ret indextest.order_and_stamp; BEGIN -- This function performs a separate query for each customer within = a customer class for the entire limit -- You sort and limit the results returned from this FOR customerid_var IN SELECT customerid FROM indextest.customerclass = WHERE classid =3D classid_in LOOP FOR oas_ret IN SELECT orderid,orderstamp FROM indextest.orders = WHERE customerid =3D customerid_var ORDER BY orderstamp DESC LIMIT = limit_in LOOP RETURN NEXT oas_ret; END LOOP; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION indextest.orders_by_class_py(classid_in = INTEGER, alimit INTEGER) RETURNS SETOF indextest.order_and_stamp AS $$ # Tables indexed by date, or columns in a table indexed by (attribute, = date) are considered as sorted lists; retrieving the first n items # in a given set of tables ordered by date (or in a set of attributes in = a table with a date index on each attribute) can be accomplished by = merging # them in sorted order using a heap. This would be a fast operation if = it weren't for the constant costs of writing it this way, which I don't = know how to avoid. from heapq import heappush, heappop, heapify import time def totime(tstamp): killtz =3D tstamp.rpartition('-')[0] killsecs =3D killtz.split('.')[0] return time.strptime(killsecs, "%Y-%m-%d %H:%M:%S") # Each sorted (attribute, date) column or table indexed by date is = represented by an object that provides a date comparison operator to be = used by the heapq implementation. # Each new row fetched requires a new query into the database (are = plpy.execute results cursors, or are they fetched wholesale into python = structures? This seemed safest.) Timestamp values=20 # are converted to python time structures, meaning additional avoidable = costs. class IterSelect: def __init__(self, get_next): self.pos =3D 0 self.get_next =3D get_next =20 self.current =3D None self.date =3D None =20 self.gnext() =20 def gnext(self): next =3D self.get_next(self.pos) if next.nrows() =3D=3D 0: raise StopIteration else: self.pos +=3D 1 self.current =3D next[0] self.date =3D totime(self.current['order_date']) =20 def __cmp__(self, iterselect_inst): return cmp(iterselect_inst.date, self.date) # The heap is used to efficiently merge the tables in sorted order.=20 def merge_sorted(gen_heap): while len(gen_heap) > 0: =20 a_gen =3D heappop(gen_heap) try: current =3D a_gen.current a_gen.gnext() heappush(gen_heap, a_gen) yield current except StopIteration: yield current =20 raise StopIteration def make_gen_heap(get_next_list): iterselect_list =3D [] for get_next in get_next_list: try: iterselect_list.append(IterSelect(get_next)) except StopIteration: pass gen_heap =3D iterselect_list heapify(gen_heap) =20 return merge_sorted(gen_heap) get_customers =3D plpy.prepare("SELECT DISTINCT customerid FROM = indextest.customerclass WHERE classid =3D $1", ["int4"]) get_order_and_date =3D plpy.prepare("SELECT orderid, orderstamp as = order_date FROM indextest.orders WHERE customerid =3D $1 ORDER BY = orderstamp DESC LIMIT 1 OFFSET $2", \ ["int4", "int4"]) def make_get_next(customer): return lambda offset: plpy.execute(get_order_and_date, = [customer["customerid"], offset]) customers =3D plpy.execute(get_customers, [classid_in]) get_nexts =3D [make_get_next(customer) for customer in customers] gen_heap =3D make_gen_heap(get_nexts) # Results are accumulated in a list (could not get generators to work) = and returned. res =3D [] traversed =3D 0 for i in gen_heap: if traversed =3D=3D alimit: return res traversed +=3D 1 res.append([i['orderid'], i['order_date']]) return res $$ LANGUAGE plpythonu; /* Commented out because this will fill your output with the results -- Sample Queries -- Use double index w/ backwards index scan for single customerid = match EXPLAIN ANALYZE SELECT orderid FROM indextest.orders WHERE customerid = =3D 9994 ORDER BY orderstamp DESC LIMIT 5; EXPLAIN ANALYZE SELECT orderid FROM indextest.orders WHERE customerid = =3D 9994 ORDER BY orderstamp DESC LIMIT 100; EXPLAIN ANALYZE SELECT orderid FROM indextest.orders WHERE customerid = =3D 1 ORDER BY orderstamp DESC LIMIT 5; EXPLAIN ANALYZE SELECT orderid FROM indextest.orders WHERE customerid = =3D 1 ORDER BY orderstamp DESC LIMIT 100; =20 -- Query Plan for all: -------------------------------------------------------------------------= -------------------------------------------------------------------------= ------------- -- Limit (cost=3D0.00..14.71 rows=3D5 width=3D12) (actual = time=3D0.059..0.125 rows=3D5 loops=3D1) -- -> Index Scan Backward using orders_customerid_orderstamp_idx on = orders (cost=3D0.00..585.28 rows=3D199 width=3D12) (actual = time=3D0.055..0.112 rows=3D5 loops=3D1) -- Index Cond: (customerid =3D 9994) =20 -- Runtime is invariant to distribution of customer orders, related = only to number of rows returned -- Average for limit 5 (both densities): 0.227 ms -- Average for limit 100 (both densities): 0.251 ms / 0.343 ms = (customerid =3D 1 only has 4 matches) =20 =20 ----- Methods for multiple customerid match / single customerclass match = ----- =20 -- The first two are our most typical use cases. When pulling even = 100 orders (which in the plpgsql function pulls 100 * number of = customers in group) -- the plpgsql function beats the normal query plan by a significant = margin. -- Other cases such as where the customer group's orders are dense in = a period of time and towards the end of the table, the python function = is able to=20 -- beat both other methods. =20 ----- Sparse/Random Case, Low Limit ----- -- =20 EXPLAIN ANALYZE SELECT o.orderid,o.orderstamp FROM indextest.orders o=20 INNER JOIN indextest.customerclass cc ON (cc.classid =3D 2)=20 WHERE o.customerid =3D cc.customerid ORDER BY o.orderstamp DESC LIMIT = 5; =20 -- Query Plan: -- = -------------------------------------------------------------------------= -------------------------------------------------------------------------= --------------- -- Limit (cost=3D350.17..2644.36 rows=3D5 width=3D12) (actual = time=3D11.093..106.100 rows=3D5 loops=3D1) -- -> Nested Loop (cost=3D350.17..786798.90 rows=3D1714 width=3D12) = (actual time=3D11.090..106.089 rows=3D5 loops=3D1) -- Join Filter: (o.customerid =3D cc.customerid) -- -> Index Scan Backward using orders_orderstamp_idx on orders = o (cost=3D0.00..15313.61 rows=3D204004 width=3D16) (actual = time=3D0.017..1.323 rows=3D621 loops=3D1) -- -> Materialize (cost=3D350.17..351.85 rows=3D168 width=3D4) = (actual time=3D0.002..0.084 rows=3D84 loops=3D621) -- -> Seq Scan on customerclass cc (cost=3D0.00..350.00 = rows=3D168 width=3D4) (actual time=3D0.429..2.387 rows=3D84 loops=3D1) -- Filter: (classid =3D 2) -- Actual Average Runtime: 10.890 ms =20 SELECT * FROM indextest.orders_by_class(2,5) ORDER BY orderstamp DESC = LIMIT 5; =20 -- Function Average Runtime: 6.201 ms SELECT * FROM indextest.orders_by_class_py(2,5); =20 -- Python Average Runtime: 12.119 ms ----- Sparse/Random Case, High Limit ----- =20 EXPLAIN ANALYZE SELECT o.orderid,o.orderstamp FROM indextest.orders o=20 INNER JOIN indextest.customerclass cc ON (cc.classid =3D 2)=20 WHERE o.customerid =3D cc.customerid ORDER BY o.orderstamp DESC LIMIT = 100; =20 -- Query Plan: -- = -------------------------------------------------------------------------= -------------------------------------------------------------- -- Limit (cost=3D5642.36..5642.61 rows=3D100 width=3D12) (actual = time=3D217.213..217.497 rows=3D100 loops=3D1) -- -> Sort (cost=3D5642.36..5646.65 rows=3D1714 width=3D12) (actual = time=3D217.210..217.311 rows=3D100 loops=3D1) -- Sort Key: o.orderstamp -- -> Hash Join (cost=3D352.10..5550.30 rows=3D1714 = width=3D12) (actual time=3D8.631..215.774 rows=3D831 loops=3D1) -- Hash Cond: (o.customerid =3D cc.customerid) -- -> Seq Scan on orders o (cost=3D0.00..4416.04 = rows=3D204004 width=3D16) (actual time=3D5.854..109.622 rows=3D101001 = loops=3D1) -- -> Hash (cost=3D350.00..350.00 rows=3D168 width=3D4) = (actual time=3D2.452..2.452 rows=3D84 loops=3D1) -- -> Seq Scan on customerclass cc = (cost=3D0.00..350.00 rows=3D168 width=3D4) (actual time=3D0.431..2.342 = rows=3D84 loops=3D1) -- Filter: (classid =3D 2) -- Actual Average Runtime: 34.463 ms =20 SELECT * FROM indextest.orders_by_class(2,100) ORDER BY orderstamp = DESC LIMIT 100; -- Function Average Runtime: 7.847 ms SELECT * FROM indextest.orders_by_class_py(2,100); -- Python Average Runtime: 27.231 ms =20 =20 ----- Dense/End of Table Case, Low Limit ----- EXPLAIN ANALYZE=20 SELECT o.orderid,o.orderstamp FROM indextest.orders o=20 INNER JOIN indextest.customerclass cc ON (cc.classid =3D 0)=20 WHERE o.customerid =3D cc.customerid ORDER BY o.orderstamp DESC LIMIT = 5; =20 -- Query Plan: -- = -------------------------------------------------------------------------= -------------------------------------------------------------------------= --------------- -- Limit (cost=3D350.19..2639.74 rows=3D5 width=3D12) (actual = time=3D14.159..81.177 rows=3D5 loops=3D1) -- -> Nested Loop (cost=3D350.19..887780.90 rows=3D1938 width=3D12) = (actual time=3D14.156..81.166 rows=3D5 loops=3D1) -- Join Filter: (o.customerid =3D cc.customerid) -- -> Index Scan Backward using orders_orderstamp_idx on orders = o (cost=3D0.00..15313.61 rows=3D204004 width=3D16) (actual = time=3D0.018..0.916 rows=3D417 loops=3D1) -- -> Materialize (cost=3D350.19..352.09 rows=3D190 width=3D4) = (actual time=3D0.002..0.097 rows=3D95 loops=3D417) -- -> Seq Scan on customerclass cc (cost=3D0.00..350.00 = rows=3D190 width=3D4) (actual time=3D0.430..2.322 rows=3D95 loops=3D1) -- Filter: (classid =3D 0) -- Actual Average Runtime: 8.717 ms SELECT * FROM indextest.orders_by_class(0,5) ORDER BY orderstamp DESC = LIMIT 5; =20 -- Function Average Runtime: 6.695 ms =20 SELECT * FROM indextest.orders_by_class_py(0,5); =20 -- Python Average Runtime: 13.724 ms ----- Dense/End of Table Case, High Limit ----- EXPLAIN ANALYZE SELECT o.orderid,o.orderstamp FROM indextest.orders o=20 INNER JOIN indextest.customerclass cc ON (cc.classid =3D 0)=20 WHERE o.customerid =3D cc.customerid ORDER BY o.orderstamp DESC LIMIT = 100; =20 -- Query Plan: -------------------------------------------------------------------------= ---------------------------------------------------------------- -- Limit (cost=3D5658.63..5658.88 rows=3D100 width=3D12) (actual = time=3D219.329..219.610 rows=3D100 loops=3D1) -- -> Sort (cost=3D5658.63..5663.47 rows=3D1938 width=3D12) (actual = time=3D219.326..219.422 rows=3D100 loops=3D1) -- Sort Key: o.orderstamp -- -> Hash Join (cost=3D352.38..5552.81 rows=3D1938 = width=3D12) (actual time=3D8.203..216.410 rows=3D1477 loops=3D1) -- Hash Cond: (o.customerid =3D cc.customerid) -- -> Seq Scan on orders o (cost=3D0.00..4416.04 = rows=3D204004 width=3D16) (actual time=3D5.738..109.593 rows=3D101001 = loops=3D1) -- -> Hash (cost=3D350.00..350.00 rows=3D190 width=3D4) = (actual time=3D2.415..2.415 rows=3D95 loops=3D1) -- -> Seq Scan on customerclass cc = (cost=3D0.00..350.00 rows=3D190 width=3D4) (actual time=3D0.431..2.293 = rows=3D95 loops=3D1) -- Filter: (classid =3D 0) -- Actual Average Runtime: 35.441 ms =20 SELECT * FROM indextest.orders_by_class(0,100) ORDER BY orderstamp = DESC LIMIT 100; -- Function Average Runtime: 9.563 ms =20 SELECT * FROM indextest.orders_by_class_py(0,100); =20 -- Python Average Runtime: 28.723 ms =20 =20 =20 =20 ----- Many Customers/Orders per Customer Class with small (5) limit = ----- =20 EXPLAIN ANALYZE SELECT o.orderid,o.orderstamp FROM indextest.orders o=20 INNER JOIN indextest.customerclass cc ON (cc.classid =3D 5)=20 WHERE o.customerid =3D cc.customerid ORDER BY o.orderstamp DESC LIMIT = 5; =20 -- Query Plan: -- = -------------------------------------------------------------------------= --------------------------------------------------------------- -- Limit (cost=3D4763.20..4765.70 rows=3D1000 width=3D12) (actual = time=3D423.610..426.400 rows=3D1000 loops=3D1) -- -> Sort (cost=3D4763.20..4767.01 rows=3D1523 width=3D12) (actual = time=3D423.607..424.565 rows=3D1000 loops=3D1) -- Sort Key: o.orderstamp -- -> Hash Join (cost=3D352.70..4682.69 rows=3D1523 = width=3D12) (actual time=3D3.012..345.063 rows=3D41099 loops=3D1) -- Hash Cond: (o.customerid =3D cc.customerid) -- -> Seq Scan on orders o (cost=3D0.00..3786.01 = rows=3D141001 width=3D16) (actual time=3D0.012..149.765 rows=3D141001 = loops=3D1) -- -> Hash (cost=3D350.00..350.00 rows=3D216 width=3D4) = (actual time=3D2.990..2.990 rows=3D308 loops=3D1) -- -> Seq Scan on customerclass cc = (cost=3D0.00..350.00 rows=3D216 width=3D4) (actual time=3D0.013..2.605 = rows=3D308 loops=3D1) -- Filter: (classid =3D 5) -- Actual Average Runtime: 2.874 ms =20 SELECT * FROM indextest.orders_by_class(5,5) ORDER BY orderstamp DESC = LIMIT 5; -- Function Average Runtime: 17.842 ms SELECT * FROM indextest.orders_by_class_py(5,5); -- Python Average Runtime: 38.154 ms =20 =20 ----- Many Customers/Orders per Customer Class with medium (200) limit = ----- =20 EXPLAIN ANALYZE SELECT o.orderid,o.orderstamp FROM indextest.orders o=20 INNER JOIN indextest.customerclass cc ON (cc.classid =3D 5)=20 WHERE o.customerid =3D cc.customerid ORDER BY o.orderstamp DESC LIMIT = 200; =20 -- Query Plan: -------------------------------------------------------------------------= ----------------------------------------------------------------- -- Limit (cost=3D4763.20..4763.70 rows=3D200 width=3D12) (actual = time=3D419.899..420.457 rows=3D200 loops=3D1) -- -> Sort (cost=3D4763.20..4767.01 rows=3D1523 width=3D12) (actual = time=3D419.897..420.088 rows=3D200 loops=3D1) -- Sort Key: o.orderstamp -- -> Hash Join (cost=3D352.70..4682.69 rows=3D1523 = width=3D12) (actual time=3D3.023..341.457 rows=3D41099 loops=3D1) -- Hash Cond: (o.customerid =3D cc.customerid) -- -> Seq Scan on orders o (cost=3D0.00..3786.01 = rows=3D141001 width=3D16) (actual time=3D0.012..146.136 rows=3D141001 = loops=3D1) -- -> Hash (cost=3D350.00..350.00 rows=3D216 width=3D4) = (actual time=3D3.003..3.003 rows=3D308 loops=3D1) -- -> Seq Scan on customerclass cc = (cost=3D0.00..350.00 rows=3D216 width=3D4) (actual time=3D0.012..2.624 = rows=3D308 loops=3D1) -- Filter: (classid =3D 5) -- Actual Average Runtime: 93.687 ms =20 SELECT * FROM indextest.orders_by_class(5,200) ORDER BY orderstamp = DESC LIMIT 200; -- Function Average Runtime: 129.787 ms SELECT * FROM indextest.orders_by_class_py(5,200); -- Python Average Runtime: 71.323 ms =20 =20 =20 ----- Many Customers/Orders per Customer Class with BIG limit ----- =20 EXPLAIN ANALYZE SELECT o.orderid,o.orderstamp FROM indextest.orders o=20 INNER JOIN indextest.customerclass cc ON (cc.classid =3D 5)=20 WHERE o.customerid =3D cc.customerid ORDER BY o.orderstamp DESC LIMIT = 1000; =20 -- Query Plan: -------------------------------------------------------------------------= ----------------------------------------------------------------- -- Limit (cost=3D4763.20..4765.70 rows=3D1000 width=3D12) (actual = time=3D423.610..426.400 rows=3D1000 loops=3D1) -- -> Sort (cost=3D4763.20..4767.01 rows=3D1523 width=3D12) (actual = time=3D423.607..424.565 rows=3D1000 loops=3D1) -- Sort Key: o.orderstamp -- -> Hash Join (cost=3D352.70..4682.69 rows=3D1523 = width=3D12) (actual time=3D3.012..345.063 rows=3D41099 loops=3D1) -- Hash Cond: (o.customerid =3D cc.customerid) -- -> Seq Scan on orders o (cost=3D0.00..3786.01 = rows=3D141001 width=3D16) (actual time=3D0.012..149.765 rows=3D141001 = loops=3D1) -- -> Hash (cost=3D350.00..350.00 rows=3D216 width=3D4) = (actual time=3D2.990..2.990 rows=3D308 loops=3D1) -- -> Seq Scan on customerclass cc = (cost=3D0.00..350.00 rows=3D216 width=3D4) (actual time=3D0.013..2.605 = rows=3D308 loops=3D1) -- Filter: (classid =3D 5) -- Actual Average Runtime: 101.939 ms =20 SELECT * FROM indextest.orders_by_class(5,1000) ORDER BY orderstamp = DESC LIMIT 1000; -- Function Average Runtime: 133.055 ms SELECT * FROM indextest.orders_by_class_py(5,1000); -- Python Average Runtime: 213.113 ms =20 =20 =20 ----- Many Customers/Orders per Customer Class with BIGGER limit ----- =20 EXPLAIN ANALYZE SELECT o.orderid,o.orderstamp FROM indextest.orders o=20 INNER JOIN indextest.customerclass cc ON (cc.classid =3D 5)=20 WHERE o.customerid =3D cc.customerid ORDER BY o.orderstamp DESC LIMIT = 10000; =20 -- Query Plan: -------------------------------------------------------------------------= ----------------------------------------------------------------- -- Limit (cost=3D4763.20..4767.01 rows=3D1523 width=3D12) (actual = time=3D419.934..447.761 rows=3D10000 loops=3D1) -- -> Sort (cost=3D4763.20..4767.01 rows=3D1523 width=3D12) (actual = time=3D419.931..429.575 rows=3D10000 loops=3D1) -- Sort Key: o.orderstamp -- -> Hash Join (cost=3D352.70..4682.69 rows=3D1523 = width=3D12) (actual time=3D2.971..341.440 rows=3D41099 loops=3D1) -- Hash Cond: (o.customerid =3D cc.customerid) -- -> Seq Scan on orders o (cost=3D0.00..3786.01 = rows=3D141001 width=3D16) (actual time=3D0.010..145.945 rows=3D141001 = loops=3D1) -- -> Hash (cost=3D350.00..350.00 rows=3D216 width=3D4) = (actual time=3D2.951..2.951 rows=3D308 loops=3D1) -- -> Seq Scan on customerclass cc = (cost=3D0.00..350.00 rows=3D216 width=3D4) (actual time=3D0.012..2.568 = rows=3D308 loops=3D1) -- Filter: (classid =3D 5) -- Actual Average Runtime: 142.393 ms =20 SELECT * FROM indextest.orders_by_class(5,10000) ORDER BY orderstamp = DESC LIMIT 10000; -- Function Average Runtime: 180.250 ms SELECT * FROM indextest.orders_by_class_py(5,10000); -- Python Average Runtime: 2000 ms */ -- Done. =20 DROP SCHEMA indextest CASCADE; ROLLBACK; ------=_NextPart_000_0F18_01C7CD8C.4D6D4230--