pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Ansgar -59cobalt- Wiechers <lists@planetcobalt.net>
To: pgsql-performance@postgresql.org
Subject: Re: Performance issue using Tsearch2
Date: Tue, 5 Feb 2008 14:36:02 +0100
Message-ID: <20080205133602.GA11028@mail.planetcobalt.net> (raw)
In-Reply-To: <caec019b0802050347t4cd46203w7d087d3d51ac2b11@mail.gmail.com>
References: <caec019b0802050347t4cd46203w7d087d3d51ac2b11@mail.gmail.com>

On 2008-02-05 Viviane Lestic wrote:
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------------------------
> Sort (cost=2345.54..2345.58 rows=16 width=308) (actual
> time=270638.774..270643.142 rows=7106 loops=1)
> Sort Key: rank(tab_ocr.zoneindex_test, q.q)
> -> Nested Loop (cost=80.04..2345.22 rows=16 width=308) (actual
> time=40886.553..270619.730 rows=7106 loops=1)
> -> Nested Loop (cost=80.04..1465.76 rows=392 width=308) (actual
> time=38209.193..173932.313 rows=272414 loops=1)
> -> Function Scan on q (cost=0.00..0.01 rows=1 width=32) (actual
> time=0.006..0.007 rows=1 loops=1)
> -> Bitmap Heap Scan on tab_ocr (cost=80.04..1460.85 rows=392
> width=276) (actual time=38209.180..173507.052 rows=272414 loops=1)
> Filter: (tab_ocr.zoneindex_test @@ q.q)
> -> Bitmap Index Scan on zoneindex_test_idx (cost=0.00..79.94 rows=392
> width=0) (actual time=38204.261..38204.261 rows=283606 loops=1)
> Index Cond: (tab_ocr.zoneindex_test @@ q.q)
> -> Index Scan using tab_chemin_label_index on tab_chemin
> (cost=0.00..2.23 rows=1 width=4) (actual time=0.036..0.036 rows=0
> loops=272414)
> Index Cond: (tab_ocr.idstruct = tab_chemin.label)
> Filter: ((chemin)::text ~~ '%;2;%'::text)
> Total runtime: 270647.946 ms
> **************************************************************************
> 
> Could someone help me analyze this problem?

Your planner estimates are way off. Try increasing the statistics target
for the columns used in this query and re-analyze the tables after doing
so.

Regards
Ansgar Wiechers
-- 
"The Mac OS X kernel should never panic because, when it does, it
seriously inconveniences the user."
--http://developer.apple.com/technotes/tn2004/tn2118.html



view thread (4+ messages)  latest in thread

Message-ID: <20080205133602.GA11028@mail.planetcobalt.net>
Permalink:  ../20080205133602.GA11028@mail.planetcobalt.net/
Also on:    postgresql.org/message-id/20080205133602.GA11028@mail.planetcobalt.net

 · 

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: lists@planetcobalt.net
  Subject: Re: Performance issue using Tsearch2
  In-Reply-To: <20080205133602.GA11028@mail.planetcobalt.net>

* 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