Re: How to estimate size of a row and therefore how much progress this query has made

Greg Stark <gsstark@mit.edu> writes:
> How do I measure the amount of space a record takes in a table?
> The table structure it's inserting into looks like this:

>       Column       |          Type          | Modifiers
> -------------------+------------------------+-----------
>  str               | character varying(100) |
>  foo_id            | integer                |
>  bar_id            | integer                |
>  dist              | double precision       |
>  geom2             | box                    |

> The last two columns are actually being populated with nulls. The first three
> columns are being populated with data. The string in the first column is
> actually always 12 characters.

Okay, so 4+12+4+4 data bytes, plus header overhead --- is this 7.3 or
something older?  Let's assume older --- 24+32+4 bytes per row, plus
the null bitmap and alignment padding; say 64 bytes per row.

> So far it's inserted quite a bit of data: [8.5 Gig]

> I think it'll insert a total of 107,535,604 records.

Should be finishing pretty darn soon; in fact, I'd venture you already
had a good bit of data in the table.   You don't have much over 6 gig
accounted for here.

> I'm also going to have to make at least one index on this table. I'm dreading
> that. How do I estimate how much space the index will take up?

As above, except the header overhead is much less (I think 8+4 bytes per
row).

            regards, tom lane