There is such a request:

SELECT * FROM file file1 LEFT JOIN file file2 ON ((((file2.transaction_code)::text = '10'::text) AND ((file1.uid)::text = (file2.uid)::text))) LEFT JOIN file file3 ON (((((file3.transaction_code)::text = '02'::text) AND ((file1.uid)::text = (file3.uid)::text)) AND (file3.send_date > file1.send_date))) LEFT JOIN file file4 ON (((((file4.transaction_code)::text = '04'::text) AND ((file1.uid)::text = (file4.uid)::text)) AND (file4.send_date > file1.send_date))) LEFT JOIN file file5 ON (((((file5.transaction_code)::text = '07'::text) AND ((file1.uid)::text = (file5.uid)::text)) AND (file5.send_date > file1.send_date))) LEFT JOIN file file6 ON ((((((file6.turn_code)::text = '07'::text) AND ((file1.uid)::text = (file6.uid)::text)) AND (file6.complete = true)) AND (file6.send_date > file1.send_date))) LEFT JOIN error ON (((file1.uid)::text = (error.uid)::text)) WHERE (((file1.turn_code)::text = '01'::text) AND ((file1.transaction_code)::text = '01'::text));

It works so slowly, you need to do something about it. The first thing that came to mind was to replace JOIN with OR where possible. It turned out such a non-working thing:

 SELECT * FROM file WHERE ((((file.transaction_code)::text = '10'::text) AND ((file.uid)::text = (file.uid)::text)) OR ((((file.transaction_code)::text = '02'::text) AND ((file.uid)::text = (file.uid)::text)) AND (file.send_date > file.send_date)) OR ((((file.transaction_code)::text = '04'::text) AND ((file.uid)::text = (file.uid)::text)) AND (file.send_date > file.send_date)) OR ((((file.transaction_code)::text = '07'::text) AND ((file.uid)::text = (file.uid)::text)) AND (file.send_date > file.send_date)) OR (((((file.turn_code)::text = '07'::text) AND ((file.uid)::text = (file.uid)::text)) AND (file.complete = true)) AND (file.send_date > file.send_date))) AND (((file.turn_code)::text = '01'::text) AND ((file.transaction_code)::text = '01'::text));

Such a thing loads a lot longer and displays an incorrect result. What to do ?

  • Why a question about query performance without DDL and without explain (analyze,buffers) ? And what do you want to do? - Fine
  • I understand your dissatisfaction, but this question is more theoretical. What basically can be done with a bunch of join 's on the same table? - faoxis
  • See explain. To think. Put down indexes, make analyze, see explain. Or in the other direction to go: to comprehend the desired result and understand what you need to get from the request. Accordingly, rewrite the request completely. - Fine
  • @faoxis All the same, you need to start with a plan ... for example, I don’t understand why such (file1.uid)::text = (file2.uid)::text) difficulties. Is it impossible to compare these fields without casting? After all, type conversion does not allow the optimizer to use an index. And by the way, is there an index at all - Mike
  • 2
    The documentation does not usually write this. Although they may write, but where, I do not know. This is what the logic and experience of communicating with other DBMS tells me. Search in a binary tree with a mismatched key format is not possible. And the documentation is not needed at all for such things. just ran a test: in postgresql 9.5.0 I created a table from 1 column (X int not null primary key), inserted 100k records into it (from generate_series). The execution plan for select * from tab where X=500 shows the work on the primary key cost 8.31. select * from tab where X::text='500' gives a complete table lookup and cost 2193 - Mike

0