Re: Inefficient query plan

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
t9id | integer |
artn | character varying |
doi | character varying |
pii | character varying |
unsp | character varying |
issueid | character(10) | not null
title | character varying(1500) | not null
titleenhancement | character varying(500) |
beginningpage | character varying(19) |
pagecount | integer | not null
documenttype | character(1) | not null
abstract | text |
Indexes:
"papers_pkey" PRIMARY KEY, btree (itemid)
"idx_papers_issueid" btree (issueid)
Foreign-key constraints:
"papers_issueid_fkey" FOREIGN KEY (issueid) REFERENCES
journals(issueid) ON DELETE CASCADE
Referenced by:
TABLE "authorkeywords" CONSTRAINT "authorkeywords_itemid_fkey"
FOREIGN KEY (itemid) REFERENCES papers(itemid) ON DELETE CASCADE
TABLE "authors" CONSTRAINT "authors_itemid_fkey" FOREIGN KEY
(itemid) REFERENCES papers(itemid) ON DELETE CASCADE
TABLE "grantnumbers" CONSTRAINT "grantnumbers_itemid_fkey" FOREIGN
KEY (itemid) REFERENCES papers(itemid) ON DELETE CASCADE
TABLE "keywordsplus" CONSTRAINT "keywordsplus_itemid_fkey" FOREIGN
KEY (itemid) REFERENCES papers(itemid) ON DELETE CASCADE
TABLE "languages" CONSTRAINT "languages_itemid_fkey" FOREIGN KEY
(itemid) REFERENCES papers(itemid) ON DELETE CASCADE
TABLE "paperreferences" CONSTRAINT "paperreferences_fromitemid_fkey"
FOREIGN KEY (itemid) REFERENCES papers(itemid) ON DELETE CASCADE

wos-db=> \d PaperReferences
Table "public.paperreferences"
Column | Type | Modifiers
--------------------+-----------------------+-----------
itemid | character varying(15) | not null
t9id | integer |
citedauthor | character varying(75) |
citedartn | character varying |
citeddoi | character varying |
citedpii | character varying |
citedunsp | character varying |
citedreferenceyear | integer |
citedtitle | character varying(20) | not null
citedvolume | character varying(4) |
citedpage | character varying(5) |
referenceindex | integer | not null
Indexes:
"paperreferences_pkey" PRIMARY KEY, btree (itemid, referenceindex)
Foreign-key constraints:
"paperreferences_fromitemid_fkey" FOREIGN KEY (itemid) REFERENCES
papers(itemid) ON DELETE CASCADE

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.

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'

The query I run:
SELECT p.ItemID FROM Papers AS p, PaperReferences AS r WHERE p.itemID =
r.ItemID AND p.issueID = 'A1983PW823'

Query plan with seqscan enabled:

"Hash Join (cost=512.71..17709356.53 rows=8283226 width=16)"
" Hash Cond: ((r.itemid)::bpchar = p.itemid)"
" -> Seq Scan on paperreferences r (cost=0.00..15110856.68
rows=670707968 width=16)"
" -> Hash (cost=500.29..500.29 rows=994 width=16)"
" -> Index Scan using idx_papers_issueid on papers p
(cost=0.00..500.29 rows=994 width=16)"
" Index Cond: (issueid = 'A1983PW823'::bpchar)"

Query plan with seqscan disbaled

"Hash Join (cost=10000000280.88..10017668625.22 rows=4233278 width=16)"
" Hash Cond: ((r.itemid)::bpchar = p.itemid)"
" -> Seq Scan on paperreferences r
(cost=10000000000.00..10015110856.68 rows=670707968 width=16)"
" -> Hash (cost=274.53..274.53 rows=508 width=16)"
" -> Index Scan using idx_papers_issueid on papers p
(cost=0.00..274.53 rows=508 width=16)"
" Index Cond: (issueid = 'A1983PW823'::bpchar)"

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.

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.

Jann

Am 23.08.10 14:08, schrieb Kevin Grittner:
> Jann Röder wrote:
> Am 23.08.10 12:18, schrieb Scott Marlowe:
>
>>> What happens if you try
>>>
>>> set enable_seqscan=off;
>>> (your query here)
>>>
>> Tried that already. The query plan is exactly the same.
>
> Exactly? Not even the cost shown for the seq scan changed?
>
> You are almost certainly omitting some crucial piece of information
> in your report. Please look over this page and post a more complete
> report. In particular, please show the results of \d for both tables
> (or of pg_dump -s -t 'tablename'), your complete postgresql.conf file
> stripped of comments, and a description of your hardware and OS.
>
> -Kevin
>