Re: Problem with LIKE-Performance - Mailing list pgsql-performance

From Hakan Kocaman
Subject Re: Problem with LIKE-Performance
Date
Msg-id 84AAD313D71B1D4F9EE20E739CC3B6EDE96F24@ATLANTIK-CL.intern.digame.de
Whole thread Raw
In response to Problem with LIKE-Performance  ("Tarabas (Manuel Rorarius)" <tarabas@tarabas.de>)
Responses Re: [bulk] RE: Problem with LIKE-Performance  ("Tarabas (Manuel Rorarius)" <tarabas@tarabas.de>)
List pgsql-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: 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
>

pgsql-performance by date:

Previous
From: "Tarabas (Manuel Rorarius)"
Date:
Subject: Re: Problem with LIKE-Performance
Next
From: REISS Thomas DSIC DESP
Date:
Subject: Re: Problem with LIKE-Performance