Message-ID: <4EB2A50F.8080009@peak6.com>
Date: Thu, 3 Nov 2011 09:28:31 -0500
From: Shaun Thomas <sthomas@peak6.com>
Reply-To: <sthomas@peak6.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; Linux i686;
	rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
To: Scott Marlowe <scott.marlowe@gmail.com>
CC: CS DBA <cs_dba@consistentstate.com>, "pgsql-performance@postgresql.org"
	<pgsql-performance@postgresql.org>
Subject: Re: Poor performance on a simple join
References: <4EB1A63E.80101@consistentstate.com>
	<CAOR=d=2hOZdnHVC-SO7rCVHYnM1dpiiM+3-zD7d_SjrEOoHtDg@mail.gmail.com>
	<4EB1BBD8.3040307@consistentstate.com>
	<CAOR=d=2zS1O8tJot7njSV1PZ8Vg07zrVuNg1FDoNmEUr8=gOMw@mail.gmail.com>
In-Reply-To: 
 <CAOR=d=2zS1O8tJot7njSV1PZ8Vg07zrVuNg1FDoNmEUr8=gOMw@mail.gmail.com>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
content-transfer-encoding: 7bit

On 11/02/2011 09:04 PM, Scott Marlowe wrote:

> Take a look here:
> http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views

Not sure materialized views are the approach I would take here. We 
actually see a lot of these kinds of queries with giant result sets, 
here. If they actually need all 12k rows for every execution (not 
likely, but possible) and 300ms is just too darn slow for that, there's 
always client-side caching.

We have a couple queries that we need to keep cached at all times. Stock 
quotes and positions, for example, have to be available in sub-ms time 
thanks to the level of parallelism involved. One query in particular 
effectively grabs the entire set of current positions and every 
optimization in the book brings its execution time down to about two 
seconds. We can't have thousands of clients executing that all the time, 
so it gets shoved into a local memcached on each webserver.

But if he's getting back 12k rows even *after* specifying a contact ID, 
a materialized view is still going to return 12k rows, and still has to 
perform at least an index scan unless he creates an MV for each contact 
ID (eww). This doesn't really look like fact-table territory either.

I think the real question is: Why isn't 300ms fast enough? Is it because 
the client executes this repeatedly? If so, what changes often enough it 
must fetch all 12k rows every single time? Would implementing a 
timestamp and only grabbing newer rows work better? Is it because of 
several connections each running it in parallel? Why not cache a local 
copy and refresh periodically? Do they actually need all 12k rows every 
time? maybe some limit and offset clauses are in order.

There's very little a human can do with 12k results. An automated tool 
shouldn't be grabbing them either, unless they're actually changing with 
every execution. If they're not, the tool really wants items since the 
last change, or it's doing too much work. If it were a report, 300ms is 
nothing compared to most reporting queries which can run for several 
minutes.

I think we're missing a lot of details here.

-- 
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com

______________________________________________

See http://www.peak6.com/email-disclaimer/ for terms and conditions related to this email