Re: [bulk] RE: [PERFORM] Problem with LIKE-Performance
Hi Hakan, HK i remember something that you need a special index with localesC. HK You nned a different operator class for this index smth. like: HK CREATE INDEX idx_image_title HK ON image HK USING btree HK (title varchar_pattern_ops); I also forgot that, thanks a lot for the hint. that speeded up my searches a lot! Best regards Manuel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [bulk] Re: [PERFORM] Problem with LIKE-Performance
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [bulk] Re: [PERFORM] Problem with LIKE-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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [bulk] Re: [bulk] Re: [PERFORM] Problem with LIKE-Performance
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
