Re: Indexing questions: Index == key? And index vs substring - how successful?
От | Listmail |
---|---|
Тема | Re: Indexing questions: Index == key? And index vs substring - how successful? |
Дата | |
Msg-id | op.trrs75azzcizji@apollo13 обсуждение исходный текст |
Ответ на | Indexing questions: Index == key? And index vs substring - how successful? (Andrew Edson <cheighlund@yahoo.com>) |
Список | pgsql-general |
> 1. Does an indexed column on a table have to be a potential primary > key? Nope, create as many index as you need/must/should. > I've been working with a couple of rather large tables where a common > select is on a foreign key called 'cntrct_id' (Varchar(9) in format). > However, the same 'cntrct_id' can appear on multiple records in the > tables I'm trying to work with now; the tables themselves record events > associated with the given 'cntrct_id' record and can store many events > for one 'cntrct_id' value. I'd thought that creating an index on the > table.cntrct_id field for the event tables would allow me to speed up > the transations some, but comparisons of time before and after the > indexing lead me to wonder if I was mistaken in this. The times were > almost identical in the following areas: Before Indexing, after Indexing > but before Analyzing, and after Analyzing. > 2. Another common sort on these fields uses part, not all, of the > 'cntrct_id' value to search for things; the first character marks > original location in an internal framework we're using, for example, and > the third character marks the month of the year that the original > 'cntrct_id' record was set up. Sorts on either of those are fairly > common as well; would indexing on the cntrct_id as a whole be able to > speed up a sort on a portion of it? Nope. This looks like suboptimal schema design... If you had an indexed date column, you would be able to make fast indexed queries with BETWEEN, >=, <=, etc.
В списке pgsql-general по дате отправления: