From: | "Ilja Golshtein" <ilejn(at)yandex(dot)ru> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | slow parsing of queries with joins |
Date: | 2005-06-15 13:41:01 |
Message-ID: | 42B02FED.000005.32675@mfront7.yandex.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | John Wells | 2005-06-15 13:47:29 | Re: Consultants |
Previous Message | Richard Huxton | 2005-06-15 13:07:58 | Re: Consultants |