
> 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

Thu, 26 Sep 2002 15:48:42 +0200
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: Insert Performance 
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


Thu, 26 Sep 2002 16:20:31 +0200
"Michael Paesold" <mpaesold@gmx.at>
Re: Insert Performance 