I did create and drop an index at some point while looking at this
issue. But I definitely reran both of the queries (and explains) after
the index was dropped, so I don't understand why there would be a
difference between the inner and left query plans. (which were run
back-to-back more than onc
"Jeremy Haile" <[EMAIL PROTECTED]> writes:
> Another random idea - does PostgreSQL do any caching of query plans?
Only if the client specifies it, either by PREPARE or the equivalent
protocol-level message. I dunno what client software you were using,
but I think few if any would PREPARE behind y
Another random idea - does PostgreSQL do any caching of query plans?
even on the session level?
I ran these queries from the same Query window, so my idea is that maybe
the inner join plan was cached prior to an automatic analyze being run.
But I'm doubting PostgreSQL would do something like
I'm pretty sure it didn't analyze in between - autovac is turned off
and I ran the test multiple times before posting.
But since I can't reproduce it anymore, I can't be 100% sure. And it
certainly doesn't make sense that the estimate for the index scan would
change based on an unrelated join
"Jeremy Haile" <[EMAIL PROTECTED]> writes:
> I still don't understand why the inner join would be so different from
> the left join prior to the analyze.
Are you sure you hadn't analyzed in between? Or maybe autovac did it
for you? The reason for the plan change is the change from estimating
1 r
The table should have been analyzed, but to make sure I ran analyze on
the table before executing the explain analyze queries. Well - problem
solved. This time the inner join query runs quickly.
I still don't understand why the inner join would be so different from
the left join prior to the a
"Jeremy Haile" <[EMAIL PROTECTED]> writes:
> I have a query made by joining two subqueries where the outer query
> performing the join takes significantly longer to run than the two
> subqueries.
Please show EXPLAIN ANALYZE results, not just EXPLAIN.
Also, have you analyzed your tables recently?
I have a query made by joining two subqueries where the outer query
performing the join takes significantly longer to run than the two
subqueries.
The first subquery runs in 600ms. The seconds subquery runs in 700ms.
But the outer query takes 240 seconds to run! Both of the two
subqueries onl
