[SQL] indexing and LIKE
Hi! If I want to be able to search for stringmatches using LIKE, doing something like the following: select id, name from table1 where lower(name) like 'somestring%'; Actually I will be joining with some other table on id too, but the join will produce a substancial amount of data to be filtered with the LIKE clause so I figure if it'd be possible to index on lower(name) somehow, it would result in an appreciated speed gain. Is it at all possible to create an index on lower(name), and in that case, what type of index and using what syntax? Is it possible to create a multicolumn index on both id and name? Both id and name are of type "text". TIA, Patrik Kudo ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] indexing and LIKE
On Thu, Oct 11, 2001 at 02:28:34PM +0200, Patrik Kudo wrote: > Hi! > > If I want to be able to search for stringmatches using LIKE, doing > something like the following: > > select id, name from table1 where lower(name) like 'somestring%'; > > Actually I will be joining with some other table on id too, but the join > will produce a substancial amount of data to be filtered with the LIKE > clause so I figure if it'd be possible to index on lower(name) somehow, > it would result in an appreciated speed gain. > > Is it at all possible to create an index on lower(name), and in that case, > what type of index and using what syntax? Is it possible to create a > multicolumn index on both id and name? Both id and name are of type > "text". Checking the short help from CREATE INDEX: template1=# \h create index Command: CREATE INDEX Description: Constructs a secondary index Syntax: CREATE [ UNIQUE ] INDEX index_name ON table [ USING acc_name ] ( column [ ops_name ] [, ...] ) CREATE [ UNIQUE ] INDEX index_name ON table [ USING acc_name ] ( func_name( column [, ... ]) [ ops_name ] ) template1=# So, you want something like: CREATE INDEX table1_l_name_idx ON table1 (lower(name)); Multicolumn indices are seldom as useful as you may think at first. And I don't think you can combine them with functional indices. Ross -- Ross Reedstrom, Ph.D. [EMAIL PROTECTED] Executive Director phone: 713-348-6166 Gulf Coast Consortium for Bioinformatics fax: 713-348-6182 Rice University MS-39 Houston, TX 77005 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] BIGINT's and indexes.
I have read somewhere (I believe in a review of Postgres 7? Can't find it right now though) that having BIGINT's as indexes (or primary keys) slows the database down, since they are 64bit and not supported that good. Is this still true? Was this ever true? Alexander. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] indexing and LIKE
On Thu, 11 Oct 2001, Patrik Kudo wrote: > Hi! > > If I want to be able to search for stringmatches using LIKE, doing > something like the following: > > select id, name from table1 where lower(name) like 'somestring%'; > > Actually I will be joining with some other table on id too, but the join > will produce a substancial amount of data to be filtered with the LIKE > clause so I figure if it'd be possible to index on lower(name) somehow, > it would result in an appreciated speed gain. You can make functional indexes: create index on table(lower()); If you're running in a locale other than C however I don't think postgres will use it in any case in 7.1 and earlier (I'm not sure about 7.2) > what type of index and using what syntax? Is it possible to create a > multicolumn index on both id and name? Both id and name are of type > "text". Yes, you can do a multicolumn index, but if you want an id and lower(name) index, it's a little more complicated and probably wouldn't do what you want (I think the functional indexes are limited to a single function with only column references as parameter). ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Server crash caused by CHECK on child
I can confirm this now works fine in current sources. No crash. > -- Hi Kevin, and everyone! > -- > -- I don't think that I only found a minor bug compared to > -- the other you wrote in your last letter: the backend crash > -- is caused by the same CHECK constraint in the child table. > -- > -- However, for you without time to analyzing Kevin's huge > -- scheme, here is the very simplified, crash-causing script. > -- > > > drop table child; > drop table ancestor; > > create table ancestor ( > node_id int4, > a int4 > ); > > create table child ( > b int4 NOT NULL DEFAULT 0 , > c int4 not null default 3, > CHECK ( child.b = 0 OR child.b = 1 ) > ) inherits (ancestor); > > insert into ancestor values (3,4); > insert into child (node_id, a, b) values (5,6,1); > > update ancestor set a=8 where node_id=5; > > - > -- > -- I am hunting it, but I have to learn all what this query-executing > -- about, so probably it takes uncomparable longer for me than for > -- a developer. > -- > -- Regards, > -- Baldvin > -- > > > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] indexing and LIKE
Patrik Kudo wrote: > [...] > > Is it at all possible to create an index on lower(name), and in that case, > what type of index and using what syntax? You'll want to look at section 7.5 "Functional Indices" in the 7.1.3 'User's Guide'. Allan. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
