From: | "Michael Paesold" <mpaesold(at)gmx(dot)at> |
---|---|
To: | "Zeugswetter Andreas SB SD" <ZeugswetterA(at)spardat(dot)at>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Insert Performance |
Date: | 2002-09-26 14:20:31 |
Message-ID: | 011201c26567$dfd302a0$4201a8c0@beeblebrox |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Zeugswetter Andreas SB SD <ZeugswetterA(at)spardat(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Curt Sampson | 2002-09-26 14:20:52 | Re: PGXLOG variable worthwhile? |
Previous Message | Zeugswetter Andreas SB SD | 2002-09-26 14:19:15 | Re: AIX compilation problems (was Re: Proposal ...) |