From: PFC <lists@peufeu.com>
To: Andrus <kobruleht2@hot.ee>
To: tv@fuzzy.cz
Cc: Richard Huxton <dev@archonet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Hash join on int takes 8..114 seconds
Date: Fri, 21 Nov 2008 20:08:27 +0100
Message-ID: <op.ukzgsdn2cigqcu@soyouz> (raw)
In-Reply-To: <EDAB641B4D004D1BB5126A75CFBDD79F@andrusnotebook>
References: <gg1sv3$27fr$1@news.hub.org>
<49253FE1.3070706@archonet.com>
<0205611B528C4C2CBA99AFC64E57D6E8@andrusnotebook>
<op.ukw669uvcigqcu@soyouz>
<75A918DFCF25470FAF96B3FE3D25A38B@andrusnotebook>
<63239.89.102.139.23.1227284110.squirrel@sq.gransy.com>
<EDAB641B4D004D1BB5126A75CFBDD79F@andrusnotebook>
> log file seems that mostly only those queries are slow:
>
> SELECT ...
> FROM dok JOIN rid USING (dokumnr)
> JOIN ProductId USING (ProductId)
> WHERE rid.ProductId LIKE :p1 || '%' AND dok.SaleDate>=:p2
>
> :p1 and :p2 are parameters different for different queries.
>
> dok contains several years of data. :p2 is usually only few previous
> months
> or last year ago.
> SELECT column list contains fixed list of known columns from all tables.
>
> How to create index or materialized view to optimize this types of
> queries ?
>
I would remove some granularity, for instance create a summary table
(materialized view) by month :
- date (contains the first day of the month)
- product_id
- total quantity, total price sold in given month
You get the idea.
If your products belong to categories, and you make queries on all the
products in a category, it could be worth making a summary table for
categories also.
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: lists@peufeu.com, kobruleht2@hot.ee, tv@fuzzy.cz, dev@archonet.com
Subject: Re: Hash join on int takes 8..114 seconds
In-Reply-To: <op.ukzgsdn2cigqcu@soyouz>
* 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