pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ken Geis <kgeis@speakeasy.org>
To: pgsql-performance@postgresql.org
Subject: plan problem
Date: Tue, 06 Apr 2004 13:25:54 -0700
Message-ID: <40731252.2030002@speakeasy.org> (raw)

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);

driver_stats.id is unique, the primary key.  The problem I'm having is 
that neither the ORDER BY nor the LIMIT change the uniqueness of that 
column, but the planner doesn't know that.  It does a HashAggregate to 
make sure the results are unique.  It thinks that 200 rows will come out 
of that operation, and then 200 rows is small enough that it thinks a 
Nested Loop is the best way to proceed from there.

I can post more query plan, but I don't think it would be that very 
helpful.  I'm considering just making a sample table and creating an 
analogous view around that.  I'd like to be able to keep this as simple 
as possible though.


Ken





view thread (5+ messages)  latest in thread

Message-ID: <40731252.2030002@speakeasy.org>
Permalink:  ../40731252.2030002@speakeasy.org/
Also on:    postgresql.org/message-id/40731252.2030002@speakeasy.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: kgeis@speakeasy.org
  Subject: Re: plan problem
  In-Reply-To: <40731252.2030002@speakeasy.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox