How to estimate size of a row and therefore how much progress this query has made
От | Greg Stark |
---|---|
Тема | How to estimate size of a row and therefore how much progress this query has made |
Дата | |
Msg-id | 878ywzs3c7.fsf@stark.dyndns.tv обсуждение исходный текст |
Ответы |
Re: How to estimate size of a row and therefore how much progress this query has made
|
Список | pgsql-general |
I'm running a long running query; it's been running since this morning. It's inserting data into a table. I'm trying to estimate how many rows it has inserted so far and how much longer it'll run. For one thing I'm running out of space on this disk... 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. Incidentally, if I set the field to a maximum of 12 will it save space? (It doesn't in Oracle for example.) And are there any table parameters that allow postgres to pack data more densely or avoid problems if I fill in the null fields later with updates? So far it's inserted quite a bit of data: -rw------- 1 postgres postgres 1.0G Feb 1 13:35 /var/lib/postgres/data/base/992336/7992140 -rw------- 1 postgres postgres 1.0G Feb 1 14:51 /var/lib/postgres/data/base/992336/7992140.1 -rw------- 1 postgres postgres 1.0G Feb 1 16:14 /var/lib/postgres/data/base/992336/7992140.2 -rw------- 1 postgres postgres 1.0G Feb 1 17:27 /var/lib/postgres/data/base/992336/7992140.3 -rw------- 1 postgres postgres 1.0G Feb 1 18:45 /var/lib/postgres/data/base/992336/7992140.4 -rw------- 1 postgres postgres 1.0G Feb 1 20:23 /var/lib/postgres/data/base/992336/7992140.5 -rw------- 1 postgres postgres 1.0G Feb 1 21:34 /var/lib/postgres/data/base/992336/7992140.6 -rw------- 1 postgres postgres 1.0G Feb 1 22:47 /var/lib/postgres/data/base/992336/7992140.7 -rw------- 1 postgres postgres 577M Feb 1 23:30 /var/lib/postgres/data/base/992336/7992140.8 I think it'll insert a total of 107,535,604 records. What I'm trying to figure out is, a) How much space will I need to make sure I have for this table and b) will it finish within the next 24 hours or should I just give up and rethink my plan. I guess I have other questions like are there any tips on making inserting lots of records fast. (Does turning off fsync help on big inserts or only when you have lots of small queries?) 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? -- greg
В списке pgsql-general по дате отправления: