Re: Problem with LIKE-Performance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx



> -----Original Message-----
> From: pgsql-performance-owner@xxxxxxxxxxxxxx 
> [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of 
> Tarabas (Manuel Rorarius)
> Sent: Tuesday, April 18, 2006 4:35 PM
> To: pgsql-performance@xxxxxxxxxxxxxx
> 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
> 


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux