What are some ideas for doing a very large query?

W

wolf-98302017-02-24 15:40:33

PostgreSQL

wolf-9830, 2017-02-24 15:40:33

There is a database query, 40 fields are taken, from different tables (9 joins) the query uses one short subquery and lead window functions, in the main table, on which the query works 5 million records, I tried to create a view, but when accessing 504 error, the same situation with the creation of a function where the cursor is used, can there be any ways to execute a very large and time-consuming request within the default timeout (30 seconds)?
PS I can’t send the request myself, the authorities don’t allow

Reply

Answer the question

In order to leave comments, you need to log in

5 answer(s)

M

Maxim Fedorov, 2017-02-24
@qonand

you need to look and analyze the query itself ... in the general case, look towards partitioning and indexes

S

sim3x, 2017-02-24
@sim3x

See EXPLAIN
Use temporary tables Take
a closer look at the authorities, or rather, their adequacy
Insert the request into a text box and replace the fields with field_1, field_x, similarly with table names

R

res2001, 2017-02-24
@res2001

1. Make the necessary indexes.
2. Break a large request into several small ones. Even if the code will seem larger, but usually several small requests are completed faster than one large one. Combine queries using temporary tables or union.
PS: In general, what you described is not something terrible. Such requests are found all the time in a variety of enterprise systems. Start with indexes, maybe you won't get to the second point.

E

Eugene Khrustalev, 2017-02-24
@eugenehr

How often does the data change?
It makes sense to run a query in the background (for example, on a schedule) and add the results to a temporary table, and then return data from it further.

A

Andrew, 2017-11-05
@byte916

It is not clear what the first piece of code is for. In it you connect the script.
Style directly on the page should be written as in your last example.
You need to do this after you have specified a link to the style, like this:

<html>
<head></head>
<body>
// Тут какой то html
// Потом файл стилей
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=PT+Sans:400,700,400italic&amp;subset=latin,cyrillic">
// Потом ваш переопределённый стиль
<style>.searchFormContainer{padding:0!important; background:transparent!important; border-radius:none!important;}</style>
<body>
<html>