Skip site navigation (1) Skip section navigation (2)

Re: text search: tablescan cost for a tsvector

From: "Marc Mamin" <M(dot)Mamin(at)intershop(dot)de>
To: "Robert Haas" <robertmhaas(at)gmail(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>, <oleg(at)sai(dot)msu(dot)su>
Subject: Re: text search: tablescan cost for a tsvector
Date: 2012-02-29 20:40:22
Message-ID: C4DAC901169B624F933534A26ED7DF3103E91862@JENMAIL01.ad.intershop.net (view raw or flat)
Thread:
Lists: pgsql-performance
> Von: Robert Haas [mailto:robertmhaas(at)gmail(dot)com]
> Gesendet: Mi 2/29/2012 7:32

>  
> On Mon, Feb 6, 2012 at 6:05 AM, Marc Mamin <M(dot)Mamin(at)intershop(dot)de> wrote:
> > without analyze: http://explain.depesz.com/s/6At
> > with analyze:    http://explain.depesz.com/s/r3B
... 
> The problem seems to be that the cost estimator doesn't know that
> detoasting is expensive.

Hello,

Tom Lane has started a follow up thread in the hacker list.
Detoasting is indeed the main obstacle, but I've repeated my test using plain storage
and the planer still choose (systematically?) the slowest query.
It seems that I bumped into 2 different issues at the same time.

http://archives.postgresql.org/pgsql-hackers/2012-02/msg00896.php

Backround: 
Our reporting system offers amongst others time histograms 
combined with a FTS filtering on error occurences (imported from error logs), 
It is hence not unusual that given search terms are found within a majority of the documents...

best regards,

Marc Mamin

In response to

pgsql-performance by date

Next:From: Marcin MirosławDate: 2012-03-01 11:45:28
Subject: [planner] Ignore "order by" in subselect if parrent do count(*)
Previous:From: Igor SchteinDate: 2012-02-29 20:37:56
Subject: Performance of SQL Function versus View

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group