Re: How to inject knowledge into a Postgres database

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(dot)holl(at)informationslogik(dot)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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461