Jann Röder<roederja@xxxxxxx> wrote: > 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? Absolutely. These are *not* the same type and don't compare all that well. > I will alter the table to use character(15) in both cases and see > if that helps. I suspect that making them the same will cure the problem, but I would recommend you make any character(n) columns character varying(n) instead of the other way around. The the character(n) data type has many surprising behaviors and tends to perform worse. Avoid using it if possible. > postgresql.conf: > max_connections = 20 > shared_buffers = 256MB > work_mem = 10MB > maintenance_work_mem = 128MB > max_stack_depth = 4MB > synchronous_commit = off > wal_buffers = 1MB > checkpoint_segments = 10 > effective_cache_size = 768MB > default_statistics_target = 200 > datestyle = 'iso, mdy' > lc_messages = 'C' > lc_monetary = 'C' > lc_numeric = 'C' > lc_time = 'C' > default_text_search_config = 'pg_catalog.simple' > Do you need an EXPLAIN ANALYZE output? Since it takes so long I > can't easily post one right now. But maybe I can get one over > night. Not necessary; you've already identified the cause and the fix. > My Hardware is an iMac running OS X 10.6.4 with 1.5 GB RAM and a > 2.1 GHz (or so) core 2 Duo processor. OK. If you still don't get a good plan, you might want to try edging up effective_cache_size, if the sum of your shared_buffers and OS cache is larger than 768MB (which I would expect it might be). If the active part of your database (the part which is frequently referenced) fits within cache space, or even a significant portion of it fits, you might need to adjust random_page_cost and perhaps seq_page_cost to reflect the lower average cost of fetching from cache rather than disk -- but you want to fix your big problem (the type mismatch) first, and then see if you need further adjustments. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
