Re: Insert Performance
От | Michael Paesold |
---|---|
Тема | Re: Insert Performance |
Дата | |
Msg-id | 011201c26567$dfd302a0$4201a8c0@beeblebrox обсуждение исходный текст |
Ответ на | Re: Insert Performance ("Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>) |
Список | pgsql-hackers |
Zeugswetter Andreas SB SD <ZeugswetterA@spardat.at> wrote: > > What could you recommend? Locking the table and selecting > > max(invoice_id) wouldn't really be much faster, with max(invoice_id) > > not using an index... > > select invoice_id from table order by invoice_id desc limit 1; > > should get you the maximum fast if you have a unique index on invoice_id. > > Andreas I've figured that out after reading the TODO about max()/min() using indexes. Thank you anyway! The second problem I had was that I have invoices here that have not been sent into accounting. An actual invoice_id is something like 210309 at the moment. So I used invoice_ids > 30000000 for "pre" invoice_ids. Having much of those "pre" invoices makes select ... desc limit 1 too slow. I figured out that I can use a partial index as a solution: CREATE INDEX idx_real_invoice_id ON invoice (invoice_id) WHERE invoice_id < 300000000; Now it works great. I have a function getNextInvoiceID(): CREATE OR REPLACE FUNCTION getNextInvoiceId() RETURNS bigint AS' DECLARE ret bigint; BEGIN LOCK TABLE invoice IN SHARE ROW EXCLUSIVE MODE; SELECT INTO ret invoice_id FROM invoice WHERE invoice_id < \'3000000000\' ORDER BY invoice_id DESC limit 1; RETURN ret + 1; END; ' LANGUAGE 'plpgsql'; Using that is nearly as fast as a regular sequence. Thanks to all of you for your help. Best Regards, Michael Paesold
В списке pgsql-hackers по дате отправления: