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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
