Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!weretis.net!feeder1.news.weretis.net!news.solani.org!.POSTED!not-for-mail From: Mladen Gogala Newsgroups: comp.databases.postgresql Subject: Re: Case-insensitive select Date: Tue, 6 Sep 2011 02:21:25 +0000 (UTC) Organization: solani.org Lines: 64 Message-ID: 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> <1315208734.245902@proxy.dienste.wien.at> <1315232045.869842@proxy.dienste.wien.at> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Trace: solani.org 1315275685 32471 eJwFwQkBwDAIA0BL4wtFDqXEv4TdhUEw6Qh4MFjz3jvQ6sVRtOKzvLrbctNKmyN0IVttM/sHJlARkg== (6 Sep 2011 02:21:25 GMT) X-Complaints-To: abuse@news.solani.org NNTP-Posting-Date: Tue, 6 Sep 2011 02:21:25 +0000 (UTC) User-Agent: Pan/0.133 (House of Butterflies) X-User-ID: eJwFwYEBgDAIA7CXoFCm5zCk/59gwiivOVmspKi8Wn+btB0kWvOFNS5zZGtuAM+2QZHxAD8mQhDX Cancel-Lock: sha1:rOhPUL7F4a2bGgTXO8xh8KcXWMo= X-NNTP-Posting-Host: eJwFwQkBwDAIA0BL0BIeOWEF/xJ2h+vqX5jDDYudEmmmkbVPaE9dq6tCPsv7xpZ755XutIM+VocruGSrF6Tj5DGE4GSn/uzAGcI= Xref: x330-a1.tempe.blueboxinc.net comp.databases.postgresql:234 On Mon, 05 Sep 2011 16:13:44 +0200, Laurenz Albe wrote: Hmmm yes, you're right, I ran your test on 9.0.2, the version available for Fedora 14 and it does have histogram on index, in additon to the histogram on the column itself. I must admit I didn't think of using the index name as a table name in pg_stats. Oracle histograms are related to table columns, not to indexes, which seems logical to me. mgogala=# CREATE TABLE test(id serial primary key, val varchar(20) NOT NULL); NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test" CREATE TABLE mgogala=# DO $$BEGIN mgogala$# FOR i IN 1..10000 LOOP mgogala$# INSERT INTO test(val) VALUES (substr(random()::text, 3)); mgogala$# END LOOP; mgogala$# END;$$; DO mgogala=# mgogala=# CREATE INDEX test_val_ind ON test(lower(val)); CREATE INDEX mgogala=# ANALYZE test; ANALYZE mgogala=# SELECT array_length(histogram_bounds, 1) FROM pg_stats mgogala-# WHERE tablename = 'test_val_ind'; array_length -------------- 101 (1 row) mgogala=# SELECT attname FROM pg_stats WHERE tablename = 'test_val_ind'; attname --------- lower (1 row) mgogala=# SELECT array_length(histogram_bounds, 1) FROM pg_stats mgogala-# where tablename='test' and attname='val'; array_length -------------- 101 (1 row) mgogala=# select version(); version -------------------------------------------------------------------------------- --------------------------- PostgreSQL 9.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.5.1 20100924 (Red Hat 4.5.1-4), 32-bit (1 row) -- http://mgogala.byethost5.com