Re: How to inject knowledge into a Postgres database - Mailing list pgsql-general

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