slow parsing of queries with joins

Поиск
Список
Период
Сортировка
От Ilja Golshtein
Тема slow parsing of queries with joins
Дата
Msg-id 42B02FED.000005.32675@mfront7.yandex.ru
обсуждение исходный текст
Ответы Re: slow parsing of queries with joins  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
Hello!

I faced with the fact PG parses some of my queries very slowly.
I mean VERY VERY slowly (for example, MS SQL is about 50 times
faster here).

I used this script for small investigation.

==
#!/usr/bin/perl

use IPC::Open3;

my $cmd = "psql -d template1";

sub execute
  {
    my($txt, $i) = @_;
    open3(*INS, *OUTS, *ERRS, "time $cmd") or die "Failed to run psql";
    print INS $txt;
    close INS;
    while(<ERRS>)
      {
        if ($i && /([0-9:.]+)user ([0-9:.]+)system ([0-9:.]+)elapsed/)
          {
            print "$3 for $i iterations\n";
          }
      }
    close OUTS;
    close ERRS;
  }

execute("DROP TABLE TST", 0);

execute("CREATE TABLE TST (F1 NUMERIC(8,3))", 0);

my $stmt = "SELECT T1.F1 FROM TST T1";

for (my $i = 2; $i<=300; $i++)
  {
    $stmt = $stmt . " INNER JOIN TST T$i ON TRUE ";
#    $stmt = $stmt . " ,TST T$i ";
    if (!($i % 50))
      {
        execute("$stmt WHERE T2.F1 IN ($stmt) ", $i);
      }
  }
==

On iteration with i==2 it produses this query
SELECT T1.F1 FROM TST T1 INNER JOIN TST T2 ON TRUE  WHERE T2.F1 IN (SELECT
T1.F1 FROM TST T1 INNER JOIN TST T2 ON TRUE )

Here is the result

0:01.05 for 50 iterations
0:05.34 for 100 iterations
0:15.80 for 150 iterations
0:33.28 for 200 iterations
1:00.52 for 250 iterations
1:49.43 for 300 iterations

On the other hand, when I use
" ,TST T$i " (this line is commented
out in the script) as increment instead
of " INNER JOIN TST T$i ON TRUE ",
I get the following result

0:00.33 for 50 iterations
0:00.98 for 100 iterations
0:01.98 for 150 iterations
0:03.40 for 200 iterations
0:05.25 for 250 iterations
0:08.73 for 300 iterations

I understand there are some
related configuration parameters.
Setting join_collapse_limit as 1
doubles speed of INNER JOIN processing,
though it is still incomparable with,
say, MS SQL.

This issue is crucial for for me since I
use a tool that generates queries with many
INNER JOIN clauses and it cannot be changed.
Is there any hope INNER JOINs could be
accelerated in, say, next release of PG?

--
Best regards
Ilja Golshtein

В списке pgsql-general по дате отправления:

Предыдущее
От: "John Wells"
Дата:
Сообщение: Re: Consultants
Следующее
От: Scott Marlowe
Дата:
Сообщение: Re: Current transaction ID?