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.
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
