From: Tarabas (Manuel Rorarius) <tarabas@tarabas.de>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: [bulk] Re: [bulk] Re: Problem with LIKE-Performance
Date: Tue, 18 Apr 2006 18:39:42 +0200
Message-ID: <779212585.20060418183942@tarabas.de> (raw)
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>
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
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: tarabas@tarabas.de, dev@archonet.com
Subject: Re: [bulk] Re: [bulk] Re: Problem with LIKE-Performance
In-Reply-To: <779212585.20060418183942@tarabas.de>
* 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