From: | Tomas Vondra <tv(at)fuzzy(dot)cz> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Poor performance on simple queries compared to sql server express |
Date: | 2013-08-27 21:37:38 |
Message-ID: | 521D1C22.1030901@fuzzy.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2013-08-28 04:10:11 | Re: SQL statement over 500% slower with 9.2 compared with 9.1 |
Previous Message | Tomas Vondra | 2013-08-27 21:27:25 | Re: SQL statement over 500% slower with 9.2 compared with 9.1 |