Re: Poor performance on simple queries compared to sql server express

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