Search Postgresql Archives

slow parsing of queries with joins

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux