pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Marc G. Fournier <scrappy@hub.org>
To: pgsql-performance@postgresql.org
Subject: mnogosearch under 7.4 ...
Date: Mon, 22 Dec 2003 17:11:25 -0400 (AST)
Message-ID: <20031222165147.S916@ganymede.hub.org> (raw)


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



Message-ID: <20031222165147.S916@ganymede.hub.org>
Permalink:  ../20031222165147.S916@ganymede.hub.org/
Also on:    postgresql.org/message-id/20031222165147.S916@ganymede.hub.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: scrappy@hub.org
  Subject: Re: mnogosearch under 7.4 ...
  In-Reply-To: <20031222165147.S916@ganymede.hub.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox