Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!newsfeed.utanet.at!newscore.univie.ac.at!aconews-feed.univie.ac.at!aconews.univie.ac.at!not-for-mail From: "Laurenz Albe" Newsgroups: comp.databases.postgresql References: <25ee8bf2-869c-427b-a4a4-5faf86f92d57@o26g2000vbi.googlegroups.com> <1314860211.105064@proxy.dienste.wien.at><1314888079.999065@proxy.dienste.wien.at><1314950920.382293@proxy.dienste.wien.at> Subject: Re: Case-insensitive select Date: Mon, 5 Sep 2011 09:45:13 +0200 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.5931 X-RFC2646: Format=Flowed; Original X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6109 Organization: MagistratWien newsserver Message-Id: <1315208734.245902@proxy.dienste.wien.at> X-Cache: nntpcache 3.0.1 (see http://www.nntpcache.org/) X-Cache: nntpcache 2.3.3 (see http://www.nntpcache.org/) Lines: 44 NNTP-Posting-Host: 141.203.254.23 X-Trace: 1315208738 aconews.univie.ac.at 10580 141.203.254.23 X-Complaints-To: abuse@univie.ac.at Xref: x330-a1.tempe.blueboxinc.net comp.databases.postgresql:231 Mladen Gogala wrote: >> At any rate statistics for such indexes are collected (look for rows in >> pg_stats that belong to the index), and these statistice are exactly the >> same as would be collected for the redundant column, so why should there >> be a difference? > The problem is in the histograms, which are collected per column, not per > index. You don't have histograms on lower(col1) unless it's in a > separate column. Why do you claim that without a test? This is PostgreSQL 9.1: CREATE TABLE test(id serial primary key, val varchar(20) NOT NULL); DO $$BEGIN FOR i IN 1..10000 LOOP INSERT INTO test(val) VALUES (substr(random()::text, 3)); END LOOP; END;$$; CREATE INDEX test_val_ind ON test(lower(val)); SHOW default_statistics_target; default_statistics_target --------------------------- 100 (1 row) ANALYZE test; SELECT array_length(histogram_bounds, 1) FROM pg_stats WHERE tablename = 'test_val_ind'; array_length -------------- 101 (1 row) Yours, Laurenz Albe