Re: [PERFORM] why query plan for the inner SELECT of WHERE x IN is wrong, but when run the inner query alone is OK?

Miernik <public@public.miernik.name> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> If necessary, turn off enable_hashjoin and enable_mergejoin so we can
>> see a comparable plan.

> After doing that it thinks like this:

> miernik=> explain select * from cnts, alog where alog.uid = cnts.uid;
> QUERY PLAN
> -----------------------------------------------------------------------------------------
> Nested Loop (cost=4.95..573640.43 rows=159220 width=76)
> -> Seq Scan on cnts (cost=0.00..36.00 rows=2000 width=39)
> -> Bitmap Heap Scan on alog (cost=4.95..285.80 rows=80 width=37)
> Recheck Cond: ((alog.uid)::integer = (cnts.uid)::integer)
> -> Bitmap Index Scan on alog_uid_idx (cost=0.00..4.93 rows=80 width=0)
> Index Cond: ((alog.uid)::integer = (cnts.uid)::integer)
> (6 rows)

> Trying EXPLAIN ANALZYE now on this makes it run forever...

It couldn't run very long if those rowcounts were accurate. How many
rows in "cnts" really? How big is "alog", and how many of its rows join
to "cnts"?

While I'm looking at this, what's the real datatypes of the uid columns?
Those explicit coercions seem a bit fishy.

> How can I bring it back to working?

It's premature to ask for a solution when we don't understand the
problem.

regards, tom lane

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance