Minor Optimization?

From: mweilguni(at)sime(dot)com
To: Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Minor Optimization?
Date: 2004-12-11 10:38:57
Message-ID: 200412111138.57548.mweilguni@sime.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I had to fix an old, badly written web application, it had some performance
problems sometimes. I found out there are some links broken:
http://...../?id=123456778995934853

in fact the link should be:
http://...../?id=1234567&78995934853

where the part &78995934853 was random and should force a reload. In the
application itself the number was only tested with a regular
expression /^\d+$/ (bad bad bad).

Unfortunatly this leads to a sequential scan with the postgresql version (7.4)
and leads to a performance problem (~300000 records).

This is a normal indexed operation (both are from a test system with far fewer
records):
EXPLAIN ANALYZE SELECT * from beitraege where id=10000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using beitraege_pkey on beitraege (cost=0.00..5.86 rows=2
width=411) (actual time=0.045..0.045 rows=0 loops=1)
Index Cond: (id = 10000)

And this is with long numbers:
cms_dev=# EXPLAIN ANALYZE SELECT * from beitraege where
id=100009999999999999999;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Seq Scan on beitraege (cost=0.00..329.26 rows=30 width=411) (actual
time=15.689..15.689 rows=0 loops=1)
Filter: ((id)::numeric = 100009999999999999999::numeric)

In that case it would be possible to rewrite the part
"id=100009999999999999999" to false, because "integer" type is never able to
hold such large numbers.

Of course the bug must be fixed at appliaction level, but this might be a
cheap optimization. I've no clue where to start looking in the source, maybe
someone can tell me where to start. Thanks!

Regards,
Mario Weilguni

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2004-12-11 16:31:07 Re: somebody working on: Prevent default re-use of sysids for dropped users and groups?
Previous Message Andrew Dunstan 2004-12-11 00:57:40 Re: regression script/makefile exit failure