X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id A575C11F625D for ; Tue, 18 Apr 2006 13:04:40 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -2.599 X-Spam-Level: X-Spam-Status: No, score=-2.599 required=5 tests=[AWL=0.000, BAYES_00=-2.599] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id pOgvZFIvAR7C for ; Tue, 18 Apr 2006 13:04:37 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.mediaskill.de (destiny.mediaskill.de [212.91.236.1]) by postgresql.org (Postfix) with ESMTP id 2E16D11F6234 for ; Tue, 18 Apr 2006 13:04:36 -0300 (ADT) Received: from localhost ([127.0.0.1]) by mail.mediaskill.de (Merak 8.3.8) with SMTP id WUW35935; Tue, 18 Apr 2006 18:04:35 +0200 Date: Tue, 18 Apr 2006 18:04:34 +0200 From: "Tarabas (Manuel Rorarius)" X-Mailer: The Bat! (v3.62.14) Professional Reply-To: "Tarabas (Manuel Rorarius)" Organization: mediaskill X-Priority: 3 (Normal) Message-ID: <492687293.20060418180434@tarabas.de> To: Tom Lane CC: pgsql-performance@postgresql.org Subject: Re: [bulk] Re: Problem with LIKE-Performance In-Reply-To: <13754.1145375139@sss.pgh.pa.us> References: <341367953.20060418163513@tarabas.de> <13458.1145373620@sss.pgh.pa.us> <653249740.20060418173459@tarabas.de> <13754.1145375139@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200604/383 X-Sequence-Number: 18467 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. 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. :-) 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 Best regards Manuel Rorarius