Re: Queries joining views

On 8/21/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Hmph ... it certainly appears to be choosing the wrong index in the
> second case. I wonder why --- can you show the relpages and reltuples
> stats from pg_class for these indexes?

I'm personally not aware how to do that, perhaps Alban will (tell me how to)
do that tomorrow.

It might be interesting also to examine the output of just
>
> explain select * from mm_insrel_table where dnumber=558332 and dir<>1
>
> with different subsets of these indexes in place. I'd like to see what
> it's deriving as the cost estimates for these indexes. If you can get
> an EXPLAIN selecting each one of the indexes, that would help diagnose
> what's going on.

Ok. Did that (with your trick, thanks!). The output is attached to
this e-mail. The script I used to drop the indexes dropped them in the
order it was using them
(partially by accident, partially because I assumed it would work in that
order).

I'm not sure if you want to see more permutations, if so please tell me.

BTW, what PG version is this exactly?

Our PG version is the version downloadable from
http://www.sunfreeware.com/programlistsparc10.html#postgresql, so 8.0.1 for
solaris sparc.

(it's a sun T2000 test machine).

regards, tom lane
>

Regards,
Wessel van Norel