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" <kobruleht2@hot.ee>) |
Список | 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.
В списке pgsql-performance по дате отправления: