From: Tomas Vondra <tv@fuzzy.cz>
To: Adam Ma'ruf <adam.maruf@gmail.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Poor performance on simple queries compared to sql server express
Date: Mon, 26 Aug 2013 15:40:15 +0200
Message-ID: <a6f3ce876cb381959e3e9657a21009c8.squirrel@sq.gransy.com> (raw)
In-Reply-To: <CAOy5j_Mmt5D4rc_sTC+6nQiKsoN9-GMRZ0_ytUx-usAYTmJVDA@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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 26 Srpen 2013, 15:02, Adam Ma'ruf wrote:
> Sure
>
> I just upgraded to 9.2.4. The query is:
> SELECT quebec_four
> , sierra
> , SUM(dollaramount) as dollaramount
> FROM alpha_quebec_echo
> GROUP BY quebec_four
> , sierra
>
> alpha_quebec_echo has 5,409,743 rows and 39 columns. Quebec_four and
> sierra are both varchar, dollar amount is a floating point field. It has
> no indexes (but neither did the mssql express table). Any other details
> you need?
>
> Thanks,
> A
Hi,
It's quite clear why the query is so slow - the plan is using on-disk sort
with ~5M rows, and that's consuming a lot of time (almost 120 seconds).
I'm wondering why it chose the sort in the first place. I'd guess it'll
choose hash aggregate, which does not require sorted input.
Can you try running "set enable_sort = false" and then explain of the query?
If that does not change the plan to "HashAggregate" instead of
"GroupAggregate", please check and post values of enable_* and cost_*
variables.
Another question is why it's doing the sort on disk and not in memory. The
explain you've posted shows it requires ~430MB on disk, and in my
experience it usually requires ~3x that much to do the in-memory sort.
I see you've set work_mem=4GB, is that correct? Can you try with a lower
value - say, 1 or 2GB? I'm not sure how this works on Windows, though.
Maybe there's some other limit (and SQL Server is not hitting it, because
it's native Windows application).
Can you prepare a testcase (table structure + data) and post it somewhere?
Or at least the structure, if it's not possible to share the data.
Also, output from "select * from pg_settings" would be helpful.
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, adam.maruf@gmail.com, pavel.stehule@gmail.com
Subject: Re: Poor performance on simple queries compared to sql server express
In-Reply-To: <a6f3ce876cb381959e3e9657a21009c8.squirrel@sq.gransy.com>
* 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