On Mon, Aug 23, 2010 at 7:19 AM, Jann Röder <roederja@ethz.ch> wrote:
> Thanks for your help,
> here is the information you requested:
>
> Table information: A = Papers, B = PaperReferences
>
> wos-db=> \d Papers
> Table "public.papers"
> Column | Type | Modifiers
> ------------------+-------------------------+-----------
> itemid | character(15) | not null
> wos-db=> \d PaperReferences
> Table "public.paperreferences"
> Column | Type | Modifiers
> --------------------+-----------------------+-----------
> itemid | character varying(15) | not null
>
> I just noticed that PaperReferences uses character varying (15) and
> Papers uses character(15). Stupid mistake of mine. Do you think this
> might cause the bad query planning? I will alter the table to use
> character(15) in both cases and see if that helps.
Almost certainly it's not helping. If the planner doesn't choose an
indexed lookup when you turn seq scans off, then either an index plan
is WAY expensive (the planner is tricked to turning off seq scan by
setting the value of them to something very high) or you don't have a
useful index.
When I asked if they were the same and if you'd tried with seqscan off
that's what I was getting at, that the types might not match.
Now, it may or may not be much faster with an index scan, depending on
your data distribution and the number of rows to be returned, but at
least if they're the same type the planner has a choice. If they're
not, it has no choice, it has to go with the seq scan.
Let us know how it runs when you've got the types matched up. BTW,
I'd generally go with text over char or varchar, but that's just me.
