From: Tomas Vondra <tv@fuzzy.cz>
To: pgsql-performance@postgresql.org
Subject: Re: Poor performance on simple queries compared to sql server express
Date: Tue, 27 Aug 2013 23:37:38 +0200
Message-ID: <521D1C22.1030901@fuzzy.cz> (raw)
In-Reply-To: <CAOy5j_P73Eb2q_XS+kHEGyWNSXMyKk0dkqgdXHCpjeF4CtPjvw@mail.gmail.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: tv@fuzzy.cz
Subject: Re: Poor performance on simple queries compared to sql server express
In-Reply-To: <521D1C22.1030901@fuzzy.cz>
* 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