Re: Temp table's effect on performance

Hello

2013/1/18 Robert James <srobertjames@gmail.com>:
> I'd like to understand better why manually using a temp table can
> improve performance so much.

one possible effect - there should be different statistic

did you look on EXPLAIN ANALYZE?

Regards

Pavel Stehule

>
> 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?
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general