From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Glaesmann <grzm@myrealbox.com>
Cc: pgsql-novice@postgresql.org
Subject: Re: Many joins: monthly summaries S-L--O--W
Date: Tue, 21 Oct 2003 13:27:34 -0400
Message-ID: <4557.1066757254@sss.pgh.pa.us> (raw)
In-Reply-To: <7C513675-03BF-11D8-B460-0005029FC1A7@myrealbox.com>
References: <7C513675-03BF-11D8-B460-0005029FC1A7@myrealbox.com>
Michael Glaesmann <grzm@myrealbox.com> writes:
> [ a very messy query ]
It does seem like that is a big query with a small query struggling to
get out. I don't have any immediate suggestions, but I wonder whether
you wouldn't profit by reading something about how to do crosstabs and
statistics in SQL. Joe Celko's book "SQL For Smarties" is an invaluable
resource for hard problems in SQL (be sure to get the 2nd edition).
Also, if you decide that a crosstab would help, take a look at
the crosstab functions in contrib/tablefunc. (Celko's book only covers
SQL-standard solutions, not methods that depend on nonstandard features,
so he's at a disadvantage when covering crosstab methods.)
Also, I do have a suggestion for this:
> current_inventory_view, showing the most recent inventory qty and date,
> is defined as
> SELECT inv.date, inv.product_code, inv.qty FROM inventory inv, (SELECT
> max(inventory.date) AS date, inventory.product_code FROM inventory
> GROUP BY inventory.product_code) curr_inv WHERE ((inv.date =
> curr_inv.date) AND (inv.product_code = curr_inv.product_code));
If you don't mind using a Postgres-specific feature, you should be able
to make this view faster by using DISTINCT ON. Look at the "weather
reports" example in the SELECT reference page.
regards, tom lane
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-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, grzm@myrealbox.com, pgsql-novice@postgresql.org
Subject: Re: Many joins: monthly summaries S-L--O--W
In-Reply-To: <4557.1066757254@sss.pgh.pa.us>
* 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