Message-ID: <44451307.2080706@archonet.com>
Date: Tue, 18 Apr 2006 17:25:43 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5 (X11/20051201)
MIME-Version: 1.0
To: "Tarabas (Manuel Rorarius)" <tarabas@tarabas.de>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org
Subject: Re: [bulk] Re: Problem with LIKE-Performance
References: <341367953.20060418163513@tarabas.de>
	<13458.1145373620@sss.pgh.pa.us>
	<653249740.20060418173459@tarabas.de>
	<13754.1145375139@sss.pgh.pa.us>
	<492687293.20060418180434@tarabas.de>
In-Reply-To: <492687293.20060418180434@tarabas.de>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tarabas (Manuel Rorarius) wrote:
> Hi Tom,
> 
> TL> As already noted, it might be worth your while to add an index using the
> TL> pattern-ops opclass to help with queries like this.
> 
> I have done that now and it works very fine as supposed.
> 
> The problem with the high startup_costs disappeared somehow after the
> change of the enable_seqscan = off and a restart of pg-admin.

I'm not sure restarting pgAdmin would have had any effect.

> first Time I ran the statement it showed 13 sec execution time.
> 
> Seq Scan on image image0_  (cost=0.00..21414.21 rows=11 width=1311)
> (actual time=10504.138..12857.127 rows=119 loops=1)
>   Filter: ((title)::text ~~ '%Davorka%'::text)
> Total runtime: 12857.372 ms
> 
> second time I ran the statement it dropped to ~500 msec , which is
> pretty ok. :-)

This will be because all the data is cached in the server's memory.

> Seq Scan on image image0_  (cost=0.00..21414.21 rows=11 width=1311)
> (actual time=270.289..552.144 rows=119 loops=1)
>   Filter: ((title)::text ~~ '%Davorka%'::text)
> Total runtime: 552.708 ms

As you can see, the plan is still scanning all the rows. In any case, 
you've changed the query - this has % at the beginning and end, which no 
index will help you with.

-- 
   Richard Huxton
   Archonet Ltd