On Tue, 2008-09-02 at 13:20 +0300, Heikki Linnakangas wrote:
Simon Riggs wrote:
It turns out that a join like this
select a.col2
from a left outer join b on a.col1 = b.col1
where b.col2 = 1;
can be cheaper if we don't remove the join, when there is an index on
a.col1 and
Simon Riggs wrote:
On Tue, 2008-09-02 at 13:20 +0300, Heikki Linnakangas wrote:
Simon Riggs wrote:
It turns out that a join like this
select a.col2
from a left outer join b on a.col1 = b.col1
where b.col2 = 1;
can be cheaper if we don't remove the join, when there is an index on
a.col1 and
Simon Riggs [EMAIL PROTECTED] writes:
Same answer, just slower. Removing the join makes the access to a into a
SeqScan, whereas it was a two-table index plan when both tables present.
The two table plan is added by the immediately preceding call add_... -
i.e. that plan is only added during
Simon Riggs wrote:
select a.col2
from a left outer join b on a.col1 = b.col1
where b.col2 = 1;
is logically equivalent to
select a.col2
from a;
No, it's not:
postgres=# CREATE TABLE a (col1 int4, col2 int4);
CREATE TABLE
postgres=# CREATE TABLE b (col1 int4, col2 int4);
CREATE TABLE
On Tue, 2008-09-02 at 10:41 +0100, Simon Riggs wrote:
On Mon, 2008-09-01 at 22:23 +0300, Heikki Linnakangas wrote:
Couldn't we also do join removal for inner joins, when there's a foreign
key reference that enforces that there's one and only one matching tuple
in the removed table:
On Tue, 2008-09-02 at 12:05 +0100, Gregory Stark wrote:
I wonder if it would be more worthwhile to remove them and have a subsequent
phase where we look for possible joins to *add*. So even if the user writes
select * from invoices where customer_id=? the planner might be able to
discover
Gregory Stark wrote:
I wonder if it would be more worthwhile to remove them and have a subsequent
phase where we look for possible joins to *add*. So even if the user writes
select * from invoices where customer_id=? the planner might be able to
discover that it can find those records quicker by
Simon Riggs wrote:
It seems I wrote my original tests using and instead of where and
hadn't noticed the distinction. Thanks for helping me catch that error.
Ah, yeah, that's a big difference. Proving correctness is hard, but to
refute something you need just one test case that fails ;-).
--
Simon Riggs [EMAIL PROTECTED] writes:
On Mon, 2008-09-01 at 22:23 +0300, Heikki Linnakangas wrote:
Did plan invalidation make it safe to rely on the presence of a unique
index for planning decisions?
My understanding was Yes and this case was the specific reason I
originally wanted to
Simon Riggs [EMAIL PROTECTED] writes:
Oh. How does the query look like after removing the join, then?
Same answer, just slower. Removing the join makes the access to a into a
SeqScan, whereas it was a two-table index plan when both tables present.
I don't really believe this: please show an
On Tue, 2008-09-02 at 12:02 -0400, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
Oh. How does the query look like after removing the join, then?
Same answer, just slower. Removing the join makes the access to a into a
SeqScan, whereas it was a two-table index plan when both
On Tue, 2008-09-02 at 17:03 +0100, Gregory Stark wrote:
Tom Lane [EMAIL PROTECTED] writes:
Simon Riggs [EMAIL PROTECTED] writes:
On Mon, 2008-09-01 at 22:23 +0300, Heikki Linnakangas wrote:
Did plan invalidation make it safe to rely on the presence of a unique
index for planning
Tom Lane [EMAIL PROTECTED] writes:
Simon Riggs [EMAIL PROTECTED] writes:
On Mon, 2008-09-01 at 22:23 +0300, Heikki Linnakangas wrote:
Did plan invalidation make it safe to rely on the presence of a unique
index for planning decisions?
My understanding was Yes and this case was the specific
Simon Riggs [EMAIL PROTECTED] writes:
As discussed on 26 June, Join Removal/Vertical Partitioning, here's a
patch to remove joins in certain circumstances.
Some points not made in the thread so far:
+ if (checkrel-rtekind != RTE_RELATION)
+ return;
This isn't right, or at
Simon Riggs [EMAIL PROTECTED] writes:
+ if (removable
+ joinrel-cheapest_total_path keeprel-cheapest_total_path)
+ {
+ elog(LOG, join removed);
+ joinrel-pathlist = keeprel-pathlist;
+ joinrel-joininfo = keeprel-baserestrictinfo;
+
On Tue, 2008-09-02 at 12:28 -0400, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
As discussed on 26 June, Join Removal/Vertical Partitioning, here's a
patch to remove joins in certain circumstances.
Some points not made in the thread so far:
Various comments accepted and agreed.
Simon Riggs [EMAIL PROTECTED] writes:
On Tue, 2008-09-02 at 12:28 -0400, Tom Lane wrote:
I haven't thought this through entirely, but wouldn't a partial index be
okay if it's marked predOK? You might be right that the case is
unlikely, but if it's only one extra line to support it ...
As of
As discussed on 26 June, Join Removal/Vertical Partitioning, here's a
patch to remove joins in certain circumstances.
Tested and blind reviewed, but this is complex and subtle enough I
welcome and expect your comments on corner cases and missed
complications. (Lord knows, I've been down a few
18 matches
Mail list logo
