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