Skip site navigation (1) Skip section navigation (2)

Re: Hash join on int takes 8..114 seconds

From: PFC <lists(at)peufeu(dot)com>
To: Andrus <kobruleht2(at)hot(dot)ee>, tv(at)fuzzy(dot)cz
Cc: "Richard Huxton" <dev(at)archonet(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Hash join on int takes 8..114 seconds
Date: 2008-11-21 19:08:27
Message-ID: op.ukzgsdn2cigqcu@soyouz (view raw or flat)
Thread:
Lists: pgsql-performance

> 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.

In response to

pgsql-performance by date

Next:From: AndrusDate: 2008-11-21 19:10:21
Subject: Re: Hash join on int takes 8..114 seconds
Previous:From: AndrusDate: 2008-11-21 19:00:09
Subject: Re: Hash join on int takes 8..114 seconds

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group