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

Greg Stark <gsstark(at)mit(dot)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