Temp table's effect on performance

From: Robert James <srobertjames(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Temp table's effect on performance
Date: 2013-01-18 17:29:46
Message-ID: CAGYyBggTfQ2Kf1VyR5g2jmqTz5a+Zmme-pAnn5V6SWbUa4xSHA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'd like to understand better why manually using a temp table can
improve performance so much.

I had one complicated query that performed well. I replaced a table
in it with a reference to a view, which was really just the table with
an inner join, and performance worsened by 2000x. Literally.

I then modified it to first manually SELECT the view into a temp
table, and performance returned to close to the original query. The
temp table had the same indexes as the original one.

How is that? What does the temp table do that the planner can't do
itself? Don't planner uses temp structures too?

In other words: Since my query is 100% identical algebraicly to not
using a temp table, why is it so much faster? Why can't the planner
work in the exact same order?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Robert James 2013-01-18 17:31:47 Understanding TIMESTAMP WITH TIME ZONE
Previous Message Tom Lane 2013-01-18 16:55:16 Re: proposal: fix corner use case of variadic fuctions usage