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
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: [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: [PERFORM] Problem with LIKE-Performance
"Tarabas (Manuel Rorarius)" <[EMAIL PROTECTED]> writes: > After removing the enable_seqscan = off and making sure it was gone, > it is a lot faster again. > Now it takes about 469.841 ms for the select. Um, no, enable_seqscan would certainly not have had any effect on the *actual* runtime of this query. All that enable_seqscan = off really does is to add a large constant to the estimated cost of any seqscan, so as to prevent the planner from selecting it unless there is no other alternative plan available. But that has nothing to do with how long the seqscan will really run. If you are seeing a speedup in repeated executions of the same seqscan plan, it's probably just a caching effect. As already noted, it might be worth your while to add an index using the pattern-ops opclass to help with queries like this. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Problem with LIKE-Performance
On 18.04.2006, at 17:16 Uhr, Tarabas (Manuel Rorarius) wrote: Is there any way to speed the like's up with a different locale than C or to get an order by in a different Locale although using the default C locale? Sure. Just create the index with create index __index on ( varchar_pattern_ops); Than you can use something like select * from where like 'Something%'; Remember that an index can't be used for queries with '%pattern%'. cug smime.p7s Description: S/MIME cryptographic signature
Re: [bulk] RE: [PERFORM] Problem with LIKE-Performance
Hi Hakan, HK> i remember something that you need a special index with locales<>"C". 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: [PERFORM] Problem with LIKE-Performance
Dave Dutcher a écrit : It looks like you are getting a sequential scan instead of an index scan. What is your locale setting? As far as I know Postgres doesn't support using indexes with LIKE unless you are using the C locale. It does if you create your index this way : CREATE INDEX idx_image_title ON image USING btree (title varchar_pattern_ops); Please see http://www.postgresql.org/docs/8.1/interactive/indexes-opclass.html Thomas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Problem with LIKE-Performance
Hi,
i remember something that you need a special index with locales<>"C".
You nned a different operator class for this index smth. like:
CREATE INDEX idx_image_title
ON image
USING btree
(title varchar_pattern_ops);
You can find the details here:
http://www.postgresql.org/docs/8.1/interactive/indexes-opclass.html
Best regards
Hakan Kocaman
Software-Development
digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln
Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: [EMAIL PROTECTED]
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of
> Tarabas (Manuel Rorarius)
> Sent: Tuesday, April 18, 2006 4:35 PM
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] Problem with LIKE-Performance
>
>
> Hi!
>
> I am having trouble with like statements on one of my tables.
>
> I already tried a vacuum and analyze but with no success.
>
> The database is PostgreSQL Database Server 8.1.3 on i686-pc-mingw32
>
> I get the following explain and I am troubled by the very high
> "startup_cost" ... does anyone have any idea why that value is so
> high?
>
> {SEQSCAN
>:startup_cost 1.00
>:total_cost 100021432.33
>:plan_rows 1
>:plan_width 1311
>:targetlist (
> {TARGETENTRY
> :expr
> {VAR
> :varno 1
> :varattno 1
> :vartype 23
> :vartypmod -1
> :varlevelsup 0
> :varnoold 1
> :varoattno 1
> }
> :resno 1
> :resname image_id
> :ressortgroupref 0
> :resorigtbl 29524
> :resorigcol 1
> :resjunk false
> }
> {TARGETENTRY
> :expr
> {VAR
> :varno 1
> :varattno 2
> :vartype 23
> :vartypmod -1
> :varlevelsup 0
> :varnoold 1
> :varoattno 2
> }
> :resno 2
> :resname customer_id
> :ressortgroupref 0
> :resorigtbl 29524
> :resorigcol 2
> :resjunk false
> }
> {TARGETENTRY
> :expr
> {VAR
> :varno 1
> :varattno 3
> :vartype 23
> :vartypmod -1
> :varlevelsup 0
> :varnoold 1
> :varoattno 3
> }
> :resno 3
> :resname theme_id
> :ressortgroupref 0
> :resorigtbl 29524
> :resorigcol 3
> :resjunk false
> }
> {TARGETENTRY
> :expr
> {VAR
> :varno 1
> :varattno 4
> :vartype 23
> :vartypmod -1
> :varlevelsup 0
> :varnoold 1
> :varoattno 4
> }
> :resno 4
> :resname gallery_id
> :ressortgroupref 0
> :resorigtbl 29524
> :resorigcol 4
> :resjunk false
> }
> {TARGETENTRY
> :expr
> {VAR
> :varno 1
> :varattno 5
> :vartype 23
> :vartypmod -1
> :varlevelsup 0
> :varnoold 1
> :varoattno 5
> }
> :resno 5
> :resname event_id
> :ressortgroupref 0
> :resorigtbl 29524
> :resorigcol 5
> :resjunk false
> }
> {TARGETENTRY
> :expr
> {VAR
> :varno 1
> :varattno 6
> :vartype 23
> :vartypmod -1
> :varlevelsup 0
> :varnoold 1
> :varoattno 6
> }
> :resno 6
> :resname width
> :ressortgroupref 0
> :resorigtbl 29524
> :resorigcol 6
> :resjunk false
> }
> {TARGETENTRY
> :expr
> {VAR
> :varno 1
> :varattno 7
> :vartype 23
> :vartypmod -1
> :varlevelsup 0
> :varnoold 1
> :varoattno 7
> }
> :resno 7
> :resname height
> :ressortgroupref 0
> :resorigtbl 29524
> :resorigcol 7
> :resjunk false
> }
> {TARGETENTRY
> :expr
> {VAR
> :varno 1
> :varattno 8
> :vartype 23
> :vartypmod -1
> :varlevelsup 0
> :varnoold 1
> :varoattno 8
> }
> :resno 8
> :resname filesize
> :ressortgroupref 0
> :resorigtbl 29524
> :resorigcol 8
> :resjunk false
> }
> {TARGETENTRY
> :expr
> {VAR
> :varno 1
> :varattno 9
> :vartype 1114
> :vartypmod -1
> :varlevelsup 0
> :varnoold 1
> :varoattno 9
> }
> :resno 9
> :resname uploadtime
> :ressortgroupref 0
> :resorigtbl 29524
> :resorigcol 9
> :resjunk false
> }
> {TARGETENTRY
> :expr
> {VAR
> :varno 1
> :varattno 10
> :vartype 1043
> :vartypmod 259
> :varlevelsup 0
>
Re: [PERFORM] Problem with LIKE-Performance
Hi Tom,
TL> "Tarabas (Manuel Rorarius)" <[EMAIL PROTECTED]> writes:
>> I get the following explain and I am troubled by the very high
>> "startup_cost" ... does anyone have any idea why that value is so
>> high?
>> {SEQSCAN
>>:startup_cost 1.00
TL> You have enable_seqscan = off, no?
You were right, I was testing this and had it removed, but somehow I
must have hit the wrong button in pgadmin and it was not successfully
removed from the database.
After removing the enable_seqscan = off and making sure it was gone,
it is a lot faster again.
Now it takes about 469.841 ms for the select.
TL> Please refrain from posting EXPLAIN VERBOSE unless it's specifically
TL> requested ...
mea culpa, i will not do that again :-)
Best regards
Manuel
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [PERFORM] Problem with LIKE-Performance
"Tarabas (Manuel Rorarius)" <[EMAIL PROTECTED]> writes:
> I get the following explain and I am troubled by the very high
> "startup_cost" ... does anyone have any idea why that value is so
> high?
> {SEQSCAN
>:startup_cost 1.00
You have enable_seqscan = off, no?
Please refrain from posting EXPLAIN VERBOSE unless it's specifically
requested ...
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] Problem with LIKE-Performance
Hi Dave, DD> It looks like you are getting a sequential scan instead of an index DD> scan. What is your locale setting? As far as I know Postgres doesn't DD> support using indexes with LIKE unless you are using the C locale. Actually no, I am using de_DE as locale because I need the german order-by support. But even for a seq-scan it seems pretty slow, but that's just a feeling. The table currently has ~172.000 rows and is suposed to rise to about 1 mio or more. Is there any way to speed the like's up with a different locale than C or to get an order by in a different Locale although using the default C locale? DD> Also, in the future you only need to post EXPLAIN ANALYZE not EXPLAIN DD> ANALYZE VERBOSE. ok, i will keep that in mind :-) didn't know how verbose you would need it *smile* Best regards Manuel ---(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: [PERFORM] Problem with LIKE-Performance
> -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-performance- > [EMAIL PROTECTED] On Behalf Of Tarabas (Manuel Rorarius) > Subject: [PERFORM] Problem with LIKE-Performance > > Hi! > > I am having trouble with like statements on one of my tables. It looks like you are getting a sequential scan instead of an index scan. What is your locale setting? As far as I know Postgres doesn't support using indexes with LIKE unless you are using the C locale. Also, in the future you only need to post EXPLAIN ANALYZE not EXPLAIN ANALYZE VERBOSE. Dave ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
