What's the datatype on naamvrouw? Notice that it's being casted to text,
which means an index on that column won't be used.
On Mon, Oct 10, 2005 at 11:29:38AM +0200, han.holl@informationslogik.nl wrote:
>
> Tom, Oleg, Yonathan,
>
> thanks for the suggestions.
> Indeed, upping the statistics from 10 to 100 helped.
>
> But order by did not:
> palga=# explain analyze select rapnaam from udps where geboortedatum =
> '1966-01-01' and naamvrouw like 'vos%' order by geboortedatum;
> QUERY PLAN
>
--------------------------------------------------------------------------------------------------------------------------
> Sort (cost=18.07..18.08 rows=1 width=18) (actual time=774.941..774.941
> rows=0 loops=1)
> Sort Key: main.geboortedatum
> -> Index Scan using nv on main (cost=0.00..18.06 rows=1 width=18) (actual
> time=746.121..746.121 rows=0 loops=1)
> Index Cond: (((naamvrouw)::text >= 'vos'::character varying) AND
> ((naamvrouw)::text < 'vot'::character varying))
> Filter: ((geboortedatum = '1966-01-01'::date) AND ((naamvrouw)::text
> ~~ 'vos%'::text))
> Total runtime: 775.068 ms
> (6 rows)
>
> I got a similar problem with a functional index, but I guess my only option is
> to create a real column with the results of the function, and replace the
> functional index with a real one.
>
> Thanks to all,
>
> Han Holl
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461