X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id A3F63D1B8CB for ; Sat, 30 Aug 2003 15:14:17 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 37422-10 for ; Sat, 30 Aug 2003 12:14:07 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id C03E2D1B8C4 for ; Sat, 30 Aug 2003 12:14:06 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h7UFE5jX025758; Sat, 30 Aug 2003 11:14:05 -0400 (EDT) To: rj@last.fm Cc: PgSQL Performance ML Subject: Re: Selecting random rows efficiently In-reply-to: <23400.1062254862@sss.pgh.pa.us> References: <200308301425.51127.rj@last.fm> <23400.1062254862@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Sat, 30 Aug 2003 10:47:42 -0400" Date: Sat, 30 Aug 2003 11:14:04 -0400 Message-ID: <25757.1062256444@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/534 X-Sequence-Number: 3350 I said: > 3. Your query now looks like > SELECT * FROM table WHERE random_id >= random() > ORDER BY random_id LIMIT 1; Correction: the above won't give quite the right query because random() is marked as a volatile function. You can hide the random() call inside a user-defined function that you (misleadingly) mark stable, or you can just stick it into a sub-select: regression=# explain select * from foo WHERE random_id >= (select random()) regression-# ORDER BY random_id LIMIT 1; QUERY PLAN ------------------------------------------------------------------------- Limit (cost=0.01..0.15 rows=1 width=8) InitPlan -> Result (cost=0.00..0.01 rows=1 width=0) -> Index Scan using fooi on foo (cost=0.00..45.50 rows=334 width=8) Index Cond: (random_id >= $0) (5 rows) This technique is probably safer against future planner changes, however: regression=# create function oneshot_random() returns float8 as regression-# 'select random()' language sql stable; CREATE FUNCTION regression=# explain select * from foo WHERE random_id >= oneshot_random() regression-# ORDER BY random_id LIMIT 1; QUERY PLAN ------------------------------------------------------------------------- Limit (cost=0.00..0.14 rows=1 width=8) -> Index Scan using fooi on foo (cost=0.00..46.33 rows=334 width=8) Index Cond: (random_id >= oneshot_random()) (3 rows) The point here is that an indexscan boundary condition has to use stable or immutable functions. By marking oneshot_random() stable, you essentially say that it's okay to evaluate it only once per query, rather than once at each row. regards, tom lane