After a long battle with technology, [EMAIL PROTECTED] (Renney Thomas), an earthling, wrote: > Has anyone any experience with PGSQL 7.x and implenting the FTC > do-not-call list - which is about 50 million 10 digit N. American > phone numbers? If so what structures have you used and what have you > done interms of performance tweaks? Is there an equivalent to Oracle's > IOT (index organized tables) in PGSQL?
There is a PostgreSQL keyword called "CLUSTER" which clusters a table according to an index. That organizes the table based on a (specified) index. All US and Canada phone numbers fit into 2^34, which is regrettably slightly larger than 2^32. It is highly unfortunate that 2^31 is only about 2.1 billion, because it would be Really Sweet to be able to express the phone numbers as 32 bit integers. Using 32 bit ints would be GREATLY efficient because that fits with common register sizes. What you might do would be to create a table like the following: create table do_not_call ( first_8_digits integer, last_digit int2 ); create index fast_index on do_not_call(first_8_digits); And you'd put the first 8 digits into the obvious field. The index would get you to the right page of the index Right Quick, and the structure will be reasonably compact. It's a useful way of thinking to try to make use of the HIGH efficiency of having a 32 bit value express most of what you need... -- let name="cbbrowne" and tld="ntlug.org" in name ^ "@" ^ tld;; http://www.ntlug.org/~cbbrowne/lisp.html Why do we drive on parkways and park on driveways? ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
