Problem with LIKE-Performance
| От | Tarabas (Manuel Rorarius) |
|---|---|
| Тема | Problem with LIKE-Performance |
| Дата | |
| Msg-id | 341367953.20060418163513@tarabas.de обсуждение исходный текст |
| Ответы |
Re: Problem with LIKE-Performance
Re: Problem with LIKE-Performance |
| Список | pgsql-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
В списке pgsql-performance по дате отправления: