Message-ID: <521D1C22.1030901@fuzzy.cz>
Date: Tue, 27 Aug 2013 23:37:38 +0200
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:17.0) Gecko/20130801 Thunderbird/17.0.8
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Poor performance on simple queries compared to sql server express
References: 
 <CAOy5j_Ok1nis33fFTKzj4haqRa3LK28jkJJby186VZyJ3jaRkA@mail.gmail.com>
 <CAFj8pRCr8Ruc9HngS_fy0KmZ+OyqMFNq2b8JLq2Y9HtowD0gNA@mail.gmail.com>
 <CAOy5j_Mmt5D4rc_sTC+6nQiKsoN9-GMRZ0_ytUx-usAYTmJVDA@mail.gmail.com>
 <a6f3ce876cb381959e3e9657a21009c8.squirrel@sq.gransy.com>
 <CAOy5j_P73Eb2q_XS+kHEGyWNSXMyKk0dkqgdXHCpjeF4CtPjvw@mail.gmail.com>
In-Reply-To: 
 <CAOy5j_P73Eb2q_XS+kHEGyWNSXMyKk0dkqgdXHCpjeF4CtPjvw@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hi,

On 27.8.2013 06:06, Adam Ma'ruf wrote:
> Hi
> 
> Thanks for the response.  I reran the query but first ran the statement
> you provided and set working mem to 2gb.  It ended up taking 133s and
> group aggregate was still used

OK.

> 
> Here are the values you asked for:
> # - Planner Method Configuration -
> # - Planner Cost Constants -

All set to default, so seems fine to me.

> 
> #seq_page_cost = 1.0# measured on an arbitrary scale
> #random_page_cost = 4.0# same scale as above
> #cpu_tuple_cost = 0.01# same scale as above
> #cpu_index_tuple_cost = 0.005# same scale as above
> #cpu_operator_cost = 0.0025# same scale as above
> #effective_cache_size = 6000MB

Well, if effective_cache_size is commented out, then it's still 128MB
(default). But I don't think that matters here.

> The output of select * from pg_statistics is large...should I attach it
> as a separate file (not sure if that's allowed on these mailing lists)

I haven't asked for pg_statistics dump. I asked for pg_settings (but I
already got most of the important pieces above).


> The data is ~2.5gb, I can't think of any place I can upload it.  I can

There's like a zillion of such places. E.g. Dropbox, Box, Wuala, Google
Drive, mega.co.nz or one of the many other alternatives. All of them
give you ~5GB space for free.

Or I could give you access to my FTP server, if that's what you prefer.

> provide the columns and data type.  it's a subset of public data from
> usaspending.gov <http://usaspending.gov>

Is there a simple way to download / filter the public data to get the
same dataset as you have?

Tomas


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance