X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id A848611F61FC for ; Tue, 18 Apr 2006 13:39:49 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.12 X-Spam-Level: X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id ZelpSLdCgpcv for ; Tue, 18 Apr 2006 13:39:44 -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 E3AF911F6059 for ; Tue, 18 Apr 2006 13:39:44 -0300 (ADT) Received: from localhost ([127.0.0.1]) by mail.mediaskill.de (Merak 8.3.8) with SMTP id WUF87542; Tue, 18 Apr 2006 18:39:42 +0200 Date: Tue, 18 Apr 2006 18:39:42 +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: <779212585.20060418183942@tarabas.de> To: Richard Huxton CC: pgsql-performance@postgresql.org Subject: Re: [bulk] Re: [bulk] Re: Problem with LIKE-Performance In-Reply-To: <44451307.2080706@archonet.com> 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> <44451307.2080706@archonet.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200604/387 X-Sequence-Number: 18471 Hi Richard, RH> As you can see, the plan is still scanning all the rows. In any case, RH> you've changed the query - this has % at the beginning and end, which no RH> index will help you with. I realize that, the index definately helped a lot with the query where the % is just at the end. The time went down to 0.203 ms after I changed the index to varchar_pattern_ops. Index Scan using idx_image_title on image (cost=0.00..6.01 rows=1 width=1311) (actual time=0.027..0.108 rows=33 loops=1) Index Cond: (((title)::text ~>=~ 'Davorka'::character varying) AND ((title)::text ~<~ 'Davorkb'::character varying)) Filter: ((title)::text ~~ 'Davorka%'::text) Total runtime: 0.203 ms Although 13 sec. for the first select seems a bit odd, I think after the Database-Cache on the Table kicks in, it should be fine with ~500 ms Best regards Manuel