Обсуждение: 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 100000000.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
}
:resno 13
:resname previewname
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 13
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 14
:vartype 1043
:vartypmod 259
:varlevelsup 0
:varnoold 1
:varoattno 14
}
:resno 14
:resname title
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 14
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 15
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 15
}
:resno 15
:resname flags
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 15
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 16
:vartype 1043
:vartypmod 259
:varlevelsup 0
:varnoold 1
:varoattno 16
}
:resno 16
:resname photographername
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 16
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 17
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 17
}
:resno 17
:resname colors
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 17
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 18
:vartype 1043
:vartypmod 68
:varlevelsup 0
:varnoold 1
:varoattno 18
}
:resno 18
:resname compression
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 18
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 19
:vartype 1043
:vartypmod 68
:varlevelsup 0
:varnoold 1
:varoattno 19
}
:resno 19
:resname resolution
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 19
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 20
:vartype 1043
:vartypmod 68
:varlevelsup 0
:varnoold 1
:varoattno 20
}
:resno 20
:resname colortype
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 20
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 21
:vartype 1043
:vartypmod 68
:varlevelsup 0
:varnoold 1
:varoattno 21
}
:resno 21
:resname colordepth
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 21
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 22
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 22
}
:resno 22
:resname sort
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 22
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 23
:vartype 1114
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 23
}
:resno 23
:resname creationtime
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 23
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 24
:vartype 1043
:vartypmod 259
:varlevelsup 0
:varnoold 1
:varoattno 24
}
:resno 24
:resname creationlocation
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 24
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 25
:vartype 25
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 25
}
:resno 25
:resname description
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 25
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 26
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 26
}
:resno 26
:resname cameravendor_id
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 26
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 27
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 27
}
:resno 27
:resname cameramodel_id
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 27
:resjunk false
}
)
:qual (
{OPEXPR
:opno 1209
:opfuncid 850
:opresulttype 16
:opretset false
:args (
{RELABELTYPE
:arg
{VAR
:varno 1
:varattno 14
:vartype 1043
:vartypmod 259
:varlevelsup 0
:varnoold 1
:varoattno 14
}
:resulttype 25
:resulttypmod -1
:relabelformat 0
}
{CONST
:consttype 25
:constlen -1
:constbyval false
:constisnull false
:constvalue 12 [ 12 0 0 0 68 97 118 111 114 107 97 37 ]
}
)
}
)
:lefttree <>
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:nParamExec 0
:scanrelid 1
}
Seq Scan on image image0_ (cost=100000000.00..100021432.33 rows=1 width=1311) (actual time=11438.273..13668.300
rows=33loops=1)
Filter: ((title)::text ~~ 'Davorka%'::text)
Total runtime: 13669.134 ms
here's my explain:
{SEQSCAN
:startup_cost 100000000.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
}
:resno 13
:resname previewname
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 13
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 14
:vartype 1043
:vartypmod 259
:varlevelsup 0
:varnoold 1
:varoattno 14
}
:resno 14
:resname title
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 14
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 15
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 15
}
:resno 15
:resname flags
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 15
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 16
:vartype 1043
:vartypmod 259
:varlevelsup 0
:varnoold 1
:varoattno 16
}
:resno 16
:resname photographername
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 16
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 17
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 17
}
:resno 17
:resname colors
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 17
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 18
:vartype 1043
:vartypmod 68
:varlevelsup 0
:varnoold 1
:varoattno 18
}
:resno 18
:resname compression
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 18
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 19
:vartype 1043
:vartypmod 68
:varlevelsup 0
:varnoold 1
:varoattno 19
}
:resno 19
:resname resolution
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 19
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 20
:vartype 1043
:vartypmod 68
:varlevelsup 0
:varnoold 1
:varoattno 20
}
:resno 20
:resname colortype
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 20
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 21
:vartype 1043
:vartypmod 68
:varlevelsup 0
:varnoold 1
:varoattno 21
}
:resno 21
:resname colordepth
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 21
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 22
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 22
}
:resno 22
:resname sort
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 22
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 23
:vartype 1114
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 23
}
:resno 23
:resname creationtime
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 23
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 24
:vartype 1043
:vartypmod 259
:varlevelsup 0
:varnoold 1
:varoattno 24
}
:resno 24
:resname creationlocation
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 24
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 25
:vartype 25
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 25
}
:resno 25
:resname description
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 25
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 26
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 26
}
:resno 26
:resname cameravendor_id
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 26
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 27
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 27
}
:resno 27
:resname cameramodel_id
:ressortgroupref 0
:resorigtbl 29524
:resorigcol 27
:resjunk false
}
)
:qual (
{OPEXPR
:opno 1209
:opfuncid 850
:opresulttype 16
:opretset false
:args (
{RELABELTYPE
:arg
{VAR
:varno 1
:varattno 14
:vartype 1043
:vartypmod 259
:varlevelsup 0
:varnoold 1
:varoattno 14
}
:resulttype 25
:resulttypmod -1
:relabelformat 0
}
{CONST
:consttype 25
:constlen -1
:constbyval false
:constisnull false
:constvalue 12 [ 12 0 0 0 68 97 118 111 114 107 97 37 ]
}
)
}
)
:lefttree <>
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:nParamExec 0
:scanrelid 1
}
Seq Scan on image image0_ (cost=100000000.00..100021432.33 rows=1 width=1311) (actual time=11438.273..13668.300
rows=33loops=1)
Filter: ((title)::text ~~ 'Davorka%'::text)
Total runtime: 13669.134 ms
The table looks like the following:
CREATE TABLE image
(
image_id int4 NOT NULL,
customer_id int4 NOT NULL,
theme_id int4,
gallery_id int4,
event_id int4,
width int4 NOT NULL,
height int4 NOT NULL,
filesize int4 NOT NULL,
uploadtime timestamp NOT NULL,
filename varchar(255) NOT NULL,
originalfilename varchar(255),
thumbname varchar(255) NOT NULL,
previewname varchar(255) NOT NULL,
title varchar(255),
flags int4 NOT NULL,
photographername varchar(255),
colors int4,
compression varchar(64),
resolution varchar(64),
colortype varchar(64),
colordepth varchar(64),
sort int4,
creationtime timestamp,
creationlocation varchar(255),
description text,
cameravendor_id int4,
cameramodel_id int4,
CONSTRAINT image_pkey PRIMARY KEY (image_id),
CONSTRAINT rel_121 FOREIGN KEY (cameravendor_id)
REFERENCES cameravendor (cameravendor_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT rel_122 FOREIGN KEY (cameramodel_id)
REFERENCES cameramodel (cameramodel_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT rel_21 FOREIGN KEY (customer_id)
REFERENCES customer (customer_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT rel_23 FOREIGN KEY (theme_id)
REFERENCES theme (theme_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT rel_26 FOREIGN KEY (gallery_id)
REFERENCES gallery (gallery_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT rel_63 FOREIGN KEY (event_id)
REFERENCES event (event_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;
These are the indexes on the table:
CREATE INDEX idx_image_customer
ON image
USING btree
(customer_id);
CREATE INDEX idx_image_event
ON image
USING btree
(event_id);
CREATE INDEX idx_image_flags
ON image
USING btree
(flags);
CREATE INDEX idx_image_gallery
ON image
USING btree
(gallery_id);
CREATE INDEX idx_image_id
ON image
USING btree
(image_id);
CREATE INDEX idx_image_id_title
ON image
USING btree
(image_id, title);
CREATE INDEX idx_image_theme
ON image
USING btree
(theme_id);
CREATE INDEX idx_image_title
ON image
USING btree
(title);
I would appreciate any hint what could be the problem here.
Best regards
Manuel Rorarius
> -----Original Message----- > From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance- > owner@postgresql.org] 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
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
"Tarabas (Manuel Rorarius)" <tarabas@tarabas.de> 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 100000000.00
You have enable_seqscan = off, no?
Please refrain from posting EXPLAIN VERBOSE unless it's specifically
requested ...
regards, tom lane
Hi Tom,
TL> "Tarabas (Manuel Rorarius)" <tarabas@tarabas.de> 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 100000000.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
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: hakan.kocaman@digame.de > -----Original Message----- > From: pgsql-performance-owner@postgresql.org > [mailto:pgsql-performance-owner@postgresql.org] 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 100000000.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 > } > :resno 13 > :resname previewname > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 13 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 14 > :vartype 1043 > :vartypmod 259 > :varlevelsup 0 > :varnoold 1 > :varoattno 14 > } > :resno 14 > :resname title > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 14 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 15 > :vartype 23 > :vartypmod -1 > :varlevelsup 0 > :varnoold 1 > :varoattno 15 > } > :resno 15 > :resname flags > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 15 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 16 > :vartype 1043 > :vartypmod 259 > :varlevelsup 0 > :varnoold 1 > :varoattno 16 > } > :resno 16 > :resname photographername > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 16 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 17 > :vartype 23 > :vartypmod -1 > :varlevelsup 0 > :varnoold 1 > :varoattno 17 > } > :resno 17 > :resname colors > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 17 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 18 > :vartype 1043 > :vartypmod 68 > :varlevelsup 0 > :varnoold 1 > :varoattno 18 > } > :resno 18 > :resname compression > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 18 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 19 > :vartype 1043 > :vartypmod 68 > :varlevelsup 0 > :varnoold 1 > :varoattno 19 > } > :resno 19 > :resname resolution > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 19 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 20 > :vartype 1043 > :vartypmod 68 > :varlevelsup 0 > :varnoold 1 > :varoattno 20 > } > :resno 20 > :resname colortype > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 20 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 21 > :vartype 1043 > :vartypmod 68 > :varlevelsup 0 > :varnoold 1 > :varoattno 21 > } > :resno 21 > :resname colordepth > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 21 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 22 > :vartype 23 > :vartypmod -1 > :varlevelsup 0 > :varnoold 1 > :varoattno 22 > } > :resno 22 > :resname sort > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 22 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 23 > :vartype 1114 > :vartypmod -1 > :varlevelsup 0 > :varnoold 1 > :varoattno 23 > } > :resno 23 > :resname creationtime > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 23 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 24 > :vartype 1043 > :vartypmod 259 > :varlevelsup 0 > :varnoold 1 > :varoattno 24 > } > :resno 24 > :resname creationlocation > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 24 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 25 > :vartype 25 > :vartypmod -1 > :varlevelsup 0 > :varnoold 1 > :varoattno 25 > } > :resno 25 > :resname description > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 25 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 26 > :vartype 23 > :vartypmod -1 > :varlevelsup 0 > :varnoold 1 > :varoattno 26 > } > :resno 26 > :resname cameravendor_id > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 26 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 27 > :vartype 23 > :vartypmod -1 > :varlevelsup 0 > :varnoold 1 > :varoattno 27 > } > :resno 27 > :resname cameramodel_id > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 27 > :resjunk false > } > ) > :qual ( > {OPEXPR > :opno 1209 > :opfuncid 850 > :opresulttype 16 > :opretset false > :args ( > {RELABELTYPE > :arg > {VAR > :varno 1 > :varattno 14 > :vartype 1043 > :vartypmod 259 > :varlevelsup 0 > :varnoold 1 > :varoattno 14 > } > :resulttype 25 > :resulttypmod -1 > :relabelformat 0 > } > {CONST > :consttype 25 > :constlen -1 > :constbyval false > :constisnull false > :constvalue 12 [ 12 0 0 0 68 97 118 111 114 107 97 37 ] > } > ) > } > ) > :lefttree <> > :righttree <> > :initPlan <> > :extParam (b) > :allParam (b) > :nParamExec 0 > :scanrelid 1 > } > > Seq Scan on image image0_ (cost=100000000.00..100021432.33 > rows=1 width=1311) (actual time=11438.273..13668.300 rows=33 loops=1) > Filter: ((title)::text ~~ 'Davorka%'::text) > Total runtime: 13669.134 ms > > > here's my explain: > > {SEQSCAN > :startup_cost 100000000.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 > } > :resno 13 > :resname previewname > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 13 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 14 > :vartype 1043 > :vartypmod 259 > :varlevelsup 0 > :varnoold 1 > :varoattno 14 > } > :resno 14 > :resname title > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 14 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 15 > :vartype 23 > :vartypmod -1 > :varlevelsup 0 > :varnoold 1 > :varoattno 15 > } > :resno 15 > :resname flags > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 15 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 16 > :vartype 1043 > :vartypmod 259 > :varlevelsup 0 > :varnoold 1 > :varoattno 16 > } > :resno 16 > :resname photographername > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 16 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 17 > :vartype 23 > :vartypmod -1 > :varlevelsup 0 > :varnoold 1 > :varoattno 17 > } > :resno 17 > :resname colors > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 17 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 18 > :vartype 1043 > :vartypmod 68 > :varlevelsup 0 > :varnoold 1 > :varoattno 18 > } > :resno 18 > :resname compression > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 18 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 19 > :vartype 1043 > :vartypmod 68 > :varlevelsup 0 > :varnoold 1 > :varoattno 19 > } > :resno 19 > :resname resolution > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 19 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 20 > :vartype 1043 > :vartypmod 68 > :varlevelsup 0 > :varnoold 1 > :varoattno 20 > } > :resno 20 > :resname colortype > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 20 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 21 > :vartype 1043 > :vartypmod 68 > :varlevelsup 0 > :varnoold 1 > :varoattno 21 > } > :resno 21 > :resname colordepth > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 21 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 22 > :vartype 23 > :vartypmod -1 > :varlevelsup 0 > :varnoold 1 > :varoattno 22 > } > :resno 22 > :resname sort > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 22 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 23 > :vartype 1114 > :vartypmod -1 > :varlevelsup 0 > :varnoold 1 > :varoattno 23 > } > :resno 23 > :resname creationtime > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 23 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 24 > :vartype 1043 > :vartypmod 259 > :varlevelsup 0 > :varnoold 1 > :varoattno 24 > } > :resno 24 > :resname creationlocation > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 24 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 25 > :vartype 25 > :vartypmod -1 > :varlevelsup 0 > :varnoold 1 > :varoattno 25 > } > :resno 25 > :resname description > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 25 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 26 > :vartype 23 > :vartypmod -1 > :varlevelsup 0 > :varnoold 1 > :varoattno 26 > } > :resno 26 > :resname cameravendor_id > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 26 > :resjunk false > } > {TARGETENTRY > :expr > {VAR > :varno 1 > :varattno 27 > :vartype 23 > :vartypmod -1 > :varlevelsup 0 > :varnoold 1 > :varoattno 27 > } > :resno 27 > :resname cameramodel_id > :ressortgroupref 0 > :resorigtbl 29524 > :resorigcol 27 > :resjunk false > } > ) > :qual ( > {OPEXPR > :opno 1209 > :opfuncid 850 > :opresulttype 16 > :opretset false > :args ( > {RELABELTYPE > :arg > {VAR > :varno 1 > :varattno 14 > :vartype 1043 > :vartypmod 259 > :varlevelsup 0 > :varnoold 1 > :varoattno 14 > } > :resulttype 25 > :resulttypmod -1 > :relabelformat 0 > } > {CONST > :consttype 25 > :constlen -1 > :constbyval false > :constisnull false > :constvalue 12 [ 12 0 0 0 68 97 118 111 114 107 97 37 ] > } > ) > } > ) > :lefttree <> > :righttree <> > :initPlan <> > :extParam (b) > :allParam (b) > :nParamExec 0 > :scanrelid 1 > } > > Seq Scan on image image0_ (cost=100000000.00..100021432.33 > rows=1 width=1311) (actual time=11438.273..13668.300 rows=33 loops=1) > Filter: ((title)::text ~~ 'Davorka%'::text) > Total runtime: 13669.134 ms > > The table looks like the following: > > CREATE TABLE image > ( > image_id int4 NOT NULL, > customer_id int4 NOT NULL, > theme_id int4, > gallery_id int4, > event_id int4, > width int4 NOT NULL, > height int4 NOT NULL, > filesize int4 NOT NULL, > uploadtime timestamp NOT NULL, > filename varchar(255) NOT NULL, > originalfilename varchar(255), > thumbname varchar(255) NOT NULL, > previewname varchar(255) NOT NULL, > title varchar(255), > flags int4 NOT NULL, > photographername varchar(255), > colors int4, > compression varchar(64), > resolution varchar(64), > colortype varchar(64), > colordepth varchar(64), > sort int4, > creationtime timestamp, > creationlocation varchar(255), > description text, > cameravendor_id int4, > cameramodel_id int4, > CONSTRAINT image_pkey PRIMARY KEY (image_id), > CONSTRAINT rel_121 FOREIGN KEY (cameravendor_id) > REFERENCES cameravendor (cameravendor_id) MATCH SIMPLE > ON UPDATE NO ACTION ON DELETE NO ACTION, > CONSTRAINT rel_122 FOREIGN KEY (cameramodel_id) > REFERENCES cameramodel (cameramodel_id) MATCH SIMPLE > ON UPDATE NO ACTION ON DELETE NO ACTION, > CONSTRAINT rel_21 FOREIGN KEY (customer_id) > REFERENCES customer (customer_id) MATCH SIMPLE > ON UPDATE NO ACTION ON DELETE NO ACTION, > CONSTRAINT rel_23 FOREIGN KEY (theme_id) > REFERENCES theme (theme_id) MATCH SIMPLE > ON UPDATE NO ACTION ON DELETE NO ACTION, > CONSTRAINT rel_26 FOREIGN KEY (gallery_id) > REFERENCES gallery (gallery_id) MATCH SIMPLE > ON UPDATE NO ACTION ON DELETE NO ACTION, > CONSTRAINT rel_63 FOREIGN KEY (event_id) > REFERENCES event (event_id) MATCH SIMPLE > ON UPDATE NO ACTION ON DELETE NO ACTION > ) > WITHOUT OIDS; > > These are the indexes on the table: > > CREATE INDEX idx_image_customer > ON image > USING btree > (customer_id); > > CREATE INDEX idx_image_event > ON image > USING btree > (event_id); > > CREATE INDEX idx_image_flags > ON image > USING btree > (flags); > > CREATE INDEX idx_image_gallery > ON image > USING btree > (gallery_id); > > CREATE INDEX idx_image_id > ON image > USING btree > (image_id); > > CREATE INDEX idx_image_id_title > ON image > USING btree > (image_id, title); > > CREATE INDEX idx_image_theme > ON image > USING btree > (theme_id); > > CREATE INDEX idx_image_title > ON image > USING btree > (title); > > > > I would appreciate any hint what could be the problem here. > > Best regards > Manuel Rorarius > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: explain analyze is your friend >
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
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 <tabname>_<column>_index on <tabname> (<column> varchar_pattern_ops); Than you can use something like select * from <table> where <column> like 'Something%'; Remember that an index can't be used for queries with '%pattern%'. cug
Вложения
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
"Tarabas (Manuel Rorarius)" <tarabas@tarabas.de> 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
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
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
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