Re: Hash join on int takes 8..114 seconds
| От | PFC |
|---|---|
| Тема | Re: Hash join on int takes 8..114 seconds |
| Дата | |
| Msg-id | op.ukzgsdn2cigqcu@soyouz обсуждение исходный текст |
| Ответ на | Re: Hash join on int takes 8..114 seconds ("Andrus") |
| Список | pgsql-performance |
Дерево обсуждения
Hash join on int takes 8..114 seconds
("Andrus",
)
Re: Hash join on int takes 8..114 seconds (PFC, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (Richard Huxton, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (Richard Huxton, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (PFC, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (PFC, )
Re: Hash join on int takes 8..114 seconds (Alan Hodgson, )
Re: Hash join on int takes 8..114 seconds (Tomas Vondra, )
Re: Hash join on int takes 8..114 seconds (Richard Huxton, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (PFC, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (Alvaro Herrera, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (Alvaro Herrera, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (Alvaro Herrera, )
Re: Hash join on int takes 8..114 seconds (Tomas Vondra, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (Scott Carey, )
Re: Hash join on int takes 8..114 seconds (PFC, )
Re: Hash join on int takes 8..114 seconds (Tom Lane, )
Re: Hash join on int takes 8..114 seconds (PFC, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (PFC, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (Tomas Vondra, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (Tomas Vondra, )
Re: Hash join on int takes 8..114 seconds (Scott Carey, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (Tomas Vondra, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (PFC, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (Richard Huxton, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (Richard Huxton, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (PFC, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (PFC, )
Re: Hash join on int takes 8..114 seconds (Alan Hodgson, )
Re: Hash join on int takes 8..114 seconds (Tomas Vondra, )
Re: Hash join on int takes 8..114 seconds (Richard Huxton, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (PFC, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (Alvaro Herrera, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (Alvaro Herrera, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (Alvaro Herrera, )
Re: Hash join on int takes 8..114 seconds (Tomas Vondra, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (Scott Carey, )
Re: Hash join on int takes 8..114 seconds (PFC, )
Re: Hash join on int takes 8..114 seconds (Tom Lane, )
Re: Hash join on int takes 8..114 seconds (PFC, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (PFC, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (Tomas Vondra, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (Tomas Vondra, )
Re: Hash join on int takes 8..114 seconds (Scott Carey, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (Tomas Vondra, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
Re: Hash join on int takes 8..114 seconds (, )
Re: Hash join on int takes 8..114 seconds ("Andrus", )
> 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.
В списке pgsql-performance по дате отправления: