X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id B27B3D1CC93 for ; Wed, 7 Apr 2004 06:03:30 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 89770-08 for ; Wed, 7 Apr 2004 06:03:30 -0300 (ADT) Received: from mail2.speakeasy.net (mail2.speakeasy.net [216.254.0.202]) by svr1.postgresql.org (Postfix) with ESMTP id 56F76D1CACC for ; Wed, 7 Apr 2004 06:03:27 -0300 (ADT) Received: (qmail 15087 invoked from network); 7 Apr 2004 09:03:28 -0000 Received: from dsl093-130-165.sfo4.dsl.speakeasy.net (HELO speakeasy.org) (kgeis@[66.93.130.165]) (envelope-sender ) by mail2.speakeasy.net (qmail-ldap-1.03) with AES256-SHA encrypted SMTP for ; 7 Apr 2004 09:03:28 -0000 Message-ID: <4073C3DF.2070307@speakeasy.org> Date: Wed, 07 Apr 2004 02:03:27 -0700 From: Ken Geis User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.5) Gecko/20031007 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Richard Huxton , pgsql-performance@postgresql.org Subject: Re: plan problem References: <40731252.2030002@speakeasy.org> <200404070938.11482.dev@archonet.com> In-Reply-To: <200404070938.11482.dev@archonet.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/78 X-Sequence-Number: 6378 Richard Huxton wrote: > On Tuesday 06 April 2004 21:25, Ken Geis wrote: > >>I am trying to find an efficient way to draw a random sample from a >>complex query. I also want it to be easy to use within my application. >> >>So I've defined a view that encapsulates the query. The id in the >>"driving" table is exposed, and I run a query like: >> >>select * from stats_record_view >> where id in (select id from driver_stats >> order by random() >> limit 30000); > > > How about a join? > > SELECT s.* > FROM > stats_record_view s > JOIN > (SELECT id FROM driver_stats ORDER BY random() LIMIT 30000) AS r > ON s.id = r.id; Yes, I tried this too after I sent the first mail, and this was somewhat better. I ended up adding a random column to the driving table, putting an index on it, and exposing that column in the view. Now I can say SELECT * FROM stats_record_view WHERE random < 0.093; For my application, it's OK if the same sample is picked time after time and it may change if data is added. ... > Also worth checking the various list archives - this has come up in the past, > but some time ago. There are some messages in the archives about how to get a random sample. I know how to do that, and that's not why I posted my message. Are you saying that the planner behavior I spoke of is in the archives? I wouldn't know what to search on to find that thread. Does anyone think that the planner issue has merit to address? Can someone help me figure out what code I would look at? Ken Geis