From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | "Tarabas (Manuel Rorarius)" <tarabas(at)tarabas(dot)de> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: [bulk] Re: Problem with LIKE-Performance |
Date: | 2006-04-18 16:25:43 |
Message-ID: | 44451307.2080706@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tarabas (Manuel Rorarius) | 2006-04-18 16:39:42 | Re: [bulk] Re: [bulk] Re: Problem with LIKE-Performance |
Previous Message | Sriram Dandapani | 2006-04-18 16:13:04 | Re: creating of temporary table takes very long |