[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
:varnoold 1
:varoattno 10
}
:resno 10
:resname filename
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 10
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 11
:vartype 1043
:vartypmod 259
:varlevelsup 0
:varnoold 1
:varoattno 11
}
:resno 11
:resname originalfilename
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 11
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 12
:vartype 1043
:vartypmod 259
:varlevelsup 0
:varnoold 1
:varoattno 12
}
:resno 12
:resname thumbname
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 12
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 13
:vartype 1043
:vartypmod 259
:varlevelsup 0
:varnoold 1
:varoattno 13
}
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
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: [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: [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
