On Mon, Aug 20, 2012 at 2:33 PM, John R Pierce <pierce@xxxxxxxxxxxx> wrote: > On 08/20/12 11:46 AM, elliott wrote: >> >> envdb=# \d astgtm2_n60e073; >> Table "public.astgtm2_n60e073" >> Column | Type | Modifiers >> --------+---------+----------- >> lat | real | >> lon | real | >> alt | integer | >> Indexes: >> "q3c_astgtm2_n60e073_idx" btree (q3c_ang2ipix(lon, lat)) CLUSTER > > > so, you DO have an index. what type does this function > q3c_ang2ipix(real,real) return ? googling it suggested a bigint, which > means every 12 byte (real,real,int) row has a corresponding b-tree entry of > 8 bytes plus b-tree stuff. I see you used cluster on this, did you do the > CLUSTER after populating the table, and before checking the > pg_total_relation_size that you reported as 20X your data ? Apparently it returns a bigint: https://www.google.com/search?q=q3c_ang2ipix&rlz=1C1CHKZ_enUS437US437&sugexp=chrome,mod=17&sourceid=chrome&ie=UTF-8 Anyways, the overhead for vary narrow tuples is going to be higher for very narrow tables. Your tuple is only 12 bytes. Each tuple has 23 bytes of overhead typically see: (http://www.postgresql.org/docs/9.1/interactive/storage-page-layout.html). Also, measuring total database size is pretty silly because there's a number of things that are global and don't increase as your data increases. To get table size, try doing SELECT pg_size_pretty(pg_relation_size('astgtm2_n60e073')); SELECT pg_size_pretty(pg_relation_size('q3c_astgtm2_n60e073_idx')); merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
