X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A1333D1B44B for ; Mon, 22 Dec 2003 21:15:35 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 84583-06 for ; Mon, 22 Dec 2003 17:14:47 -0400 (AST) Received: from ganymede.hub.org (u46n208.hfx.eastlink.ca [24.222.46.208]) by svr1.postgresql.org (Postfix) with ESMTP id A26E0D1B4E0 for ; Mon, 22 Dec 2003 17:14:45 -0400 (AST) Received: by ganymede.hub.org (Postfix, from userid 1000) id 21E7638B92; Mon, 22 Dec 2003 17:11:25 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id 1835A386C7 for ; Mon, 22 Dec 2003 17:11:25 -0400 (AST) Date: Mon, 22 Dec 2003 17:11:25 -0400 (AST) From: "Marc G. Fournier" To: pgsql-performance@postgresql.org Subject: mnogosearch under 7.4 ... Message-ID: <20031222165147.S916@ganymede.hub.org> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/293 X-Sequence-Number: 5153 G'day all ... Dave asked me today about 'slow downs' on the search engines, so am looking at the various queries generated by enabling log_statement/log_duration, to get a feel for is something is "off" ... and the following seems a bit weird ... QueryA and QueryB are the same query, but against two different tables in the databases ... QueryA takes ~4x longer to run then QueryB, but both EXPLAINs look similar ... in fact, looking at the EXPLAIN ANALYZE output, I would expect that QueryB would be the slower of the two ... but, the actual vs estimated times for ndict5/ndict4 seem off (ndict4 is estimated high, ndict5 is estimated low) ... QueryA: 186_archives=# explain analyze SELECT ndict5.url_id,ndict5.intag FROM ndict5, url WHERE ndict5.word_id=1343124681 AND url.rec_id=ndict5.url_id AND ((url.url || '') LIKE 'http://archives.postgresql.org/%%'); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.00..69799.69 rows=44 width=8) (actual time=113.067..26477.672 rows=14112 loops=1) -> Index Scan using n5_word on ndict5 (cost=0.00..34321.89 rows=8708 width=8) (actual time=27.349..25031.666 rows=15501 loops=1) Index Cond: (word_id = 1343124681) -> Index Scan using url_rec_id on url (cost=0.00..4.06 rows=1 width=4) (actual time=0.061..0.068 rows=1 loops=15501) Index Cond: (url.rec_id = "outer".url_id) Filter: ((url || ''::text) ~~ 'http://archives.postgresql.org/%%'::text) Total runtime: 26550.566 ms (7 rows) QueryB: 186_archives=# explain analyze SELECT ndict4.url_id,ndict4.intag FROM ndict4, url WHERE ndict4.word_id=-2038735111 AND url.rec_id=ndict4.url_id AND ((url.url || '') LIKE 'http://archives.postgresql.org/%%'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Nested Loop (cost=0.00..99120.97 rows=62 width=8) (actual time=26.330..6630.581 rows=2694 loops=1) -> Index Scan using n4_word on ndict4 (cost=0.00..48829.52 rows=12344 width=8) (actual time=7.954..6373.098 rows=2900 loops=1) Index Cond: (word_id = -2038735111) -> Index Scan using url_rec_id on url (cost=0.00..4.06 rows=1 width=4) (actual time=0.059..0.066 rows=1 loops=2900) Index Cond: (url.rec_id = "outer".url_id) Filter: ((url || ''::text) ~~ 'http://archives.postgresql.org/%%'::text) Total runtime: 6643.462 ms (7 rows) ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664