Question 285306 in PostgreSQL Question posted in
The official documentation can be found here.

How can I optimize a database query in PostgreSQL

I am making an application that will select components for a computer for a certain amount.

ER-diagramm:

I make the following request:

SELECT computercases.name, datastorage.name, motherboards.name, powersupply.name, processors.name, ram_memory.name,
    (computercases.price + motherboards.price + graphicscards.price + processors.price + ram_memory.price + 
    datastorage.price + powersupply.price) AS ЦЕНА
    FROM computercases
    JOIN computercasesmotherboards ON computercases.id = computercasesmotherboards.computercasesid
    JOIN motherboards ON computercasesmotherboards.motherboardsid = motherboards.id
    JOIN graphicscardsmotherboards ON motherboards.id = graphicscardsmotherboards.motherboardsid
    JOIN graphicscards ON graphicscardsmotherboards.graphicscardsid = graphicscards.id
    JOIN processorsmotherboards ON motherboards.id = processorsmotherboards.motherboardsid
    JOIN processors ON processorsmotherboards.processorsid = processors.id
    JOIN ram_memorymotherboards ON motherboards.id = ram_memorymotherboards.motherboardsid
    JOIN ram_memory ON ram_memorymotherboards.ram_memoryid = ram_memory.id
    JOIN powersupply ON powersupply.power >= graphicscards.powerconsumption
    CROSS JOIN datastorage
    WHERE (computercases.price + motherboards.price + graphicscards.price +
        processors.price + ram_memory.price + datastorage.price + powersupply.price) >= my_price-1000 and
        (computercases.price + motherboards.price + graphicscards.price +
        processors.price + ram_memory.price + datastorage.price + powersupply.price) <= my_price+1000;

But since there is a lot of data in the database, the query is executed for a very long time.

I tried create index:

CREATE INDEX CPrice ON computercases(price);
CREATE INDEX DPrice ON datastorage(price);
CREATE INDEX GPrice ON graphicscards(price);
CREATE INDEX MPrice ON motherboards(price);
CREATE INDEX PPrice ON powersupply(price);
CREATE INDEX PrPrice ON processors(price);
CREATE INDEX RPrice ON ram_memory(price);
CREATE INDEX GIndex ON graphicscards(powerconsumption);
CREATE INDEX PIndex ON powersupply(power);

But unsuccessfully, the request is executed for a very long time. Maybe I can use LIMIT or something else to optimize the query.

Any help or guidance would be greatly appreciated!

Tags:

2

Answers


  1. 0

    Did you try first to see the query plan of your SQL?
    https://www.postgresql.org/docs/current/using-explain.html – that could help a bit. It shows you the number of index scans, the duration of every subquery and gives a lot of insights about the query execution.

    Also try ANALYZE to actually run the query if you have test env to see how it performs on the real table. https://www.postgresql.org/docs/current/using-explain.html#USING-EXPLAIN-ANALYZE

    Generally speaking your query looks super cumbersome, maybe it makes sense to use materialized views https://www.postgresql.org/docs/current/rules-materializedviews.html or review the end column set that you would need to reduce amount of data returned

    Login or Signup to reply.
  2. 0

    I think, firs try create indexes for JOIN operations. For example

    graphicscardsmotherboards(motherboardsid,graphicscardsid)
    and
    computercasesmotherboards(computerCasesId,motherboardsid)

    and so on.

    Where clause in your case always need full scan. Optimization for this part of query quite impossible.
    Indexes on Price are useless.

    This CROSS JOIN datastorage – it is unclear what there wanted. In this form – very expensive.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search