0

I have following table:

CREATE TABLE public.shop_prices
(
    shop_name text COLLATE pg_catalog."default",
    product text COLLATE pg_catalog."default",
    product_category text COLLATE pg_catalog."default",
    price text COLLATE pg_catalog."default"
)

and for this table i have a dataset from 18 months. In each file there are about 15M records. I have to some analysis, like in which month a shop has increased or decreased their price. I imported two months in a table and run following query just to test:

select shop, product from shop_prices group by shop, product limit 10

I waited more than 5 minutes, but no any result and response. It was still on working. What is the best way the store these datasets and run efficiency queries? Is it a good idea if I create for each dataset a seperate tables?

Improve this question

2 Answers 2

Reset to default
1

Using explain analyze select shop_name, product from shop_prices group by shop, product limit 10 you can see how Postgres is planning and executing the query and the time the execution takes. You'll see it needs to read the whole table (with the time consuming disk reads) and then sort it in memory - which will probably need to be cached on disk, before returning the results. In the next run you might discover the same query is very snappy if the number of shop_name+product combinations are very limited and thus stored in pg_stats after that explain analyze. The point being that a simple query like this can be deceiving.

You will faster execution by creating an index on the columns you are using (create index shop_prices_shop_prod_idx on public.shop_prices(shop_name,product)).

You should definitely change the price column type to numeric (or float/float8)) if you plan to do any numerical calculations on it.

Having said all that, I suspect this table is not what you will be using as it does not have any timestamp to compare prices between months to begin with.

I suggest you complete the table design and speculate on indices to improve performance. You might even want consider table partitioning https://www.postgresql.org/docs/current/ddl-partitioning.html

You will probably be doing all sorts of queries on this data so there is no simple solution to them all.

By all means return with perhaps more specific questions with complete table description and the output from the explain analyze statement for queries you are trying out and get some good advice.

Best regards,
Bjarni

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Created indexes and partiotioned the table, works great. Thanks!
1

What is your PostgreSQL version ? First there is a typo: column shop should be shop_name. Second you query looks strange because it has only a LIMIT clause without any ORDER BY clause or WHERE clause: do you really want to have "random" rows for this query ?

Can you try to post EXPLAIN output for the SQL statement:

explain select shop_name, product from shop_prices group by shop_name, product limit 10;

Can you also check if any statistics have been computed for this table with:

select * from pg_stats where tablename='shop_prices';
Improve this answer

1 Comment

I use postgresql 12. Yes i just edited manuel, that's why i had that typo. the explain output: prnt.sc/r3yoiv and yes there 4 different statistics for this table.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.