Обсуждение: Creating indexes
I have a PHP 5 app using pgsql 8.2 and a HTML table of clients now grown to almost 10,000 loading 25 per page. There is a filter feature atop all seven columns in the table listing (all varchar except one date column). Also, sorting can be done by clicking any column header. Some complain of speed during filtering or clearing the filter. I want to create some indexes to see if this will help as I'm sure it will since there are none currently. Now my question, would it be better to create one index with all columns in the table -or- a separate index for each column field? I was assuming the latter, but would the index with all columns be beneficial as well? Thanks in advance! -- Robert
Robert Fitzpatrick wrote: > Now my question, would it be better to create one index with all columns > in the table -or- a separate index for each column field? I was assuming > the latter, but would the index with all columns be beneficial as well? Generally it's much better to have an index deal with only one column. Also I wouldn't just randomly throw an index on every column. You should only index the columns that used for the constraints. You might benefit from turning up the logging and looking at what queries are slow, then play with them and viewing their explain output with / without indexes. Extra indexes that aren't really doing anything can actually hurt performance during vacuum or insert / update / delete. Matt
Robert Fitzpatrick escreveu: > I have a PHP 5 app using pgsql 8.2 and a HTML table of clients now grown > to almost 10,000 loading 25 per page. There is a filter feature atop all > seven columns in the table listing (all varchar except one date column). > Also, sorting can be done by clicking any column header. Some complain You can try transfer the sort loading to the client, using tablesort: http://tablesorter.com/docs/ I dont know if the performance will be ok with 10k rows but... Also, you can create the various tables in background, one html for each sorted column. When the user clicks, you show the desired html. -- []´s, André Volpato Ecom Tecnologia LTDA - Análise e Desenvolvimento andre.volpato@ecomtecnologia.com.br
On Dec 10, 2007 10:53 AM, Robert Fitzpatrick <lists@webtent.net> wrote: > I have a PHP 5 app using pgsql 8.2 and a HTML table of clients now grown > to almost 10,000 loading 25 per page. There is a filter feature atop all > seven columns in the table listing (all varchar except one date column). > Also, sorting can be done by clicking any column header. Some complain > of speed during filtering or clearing the filter. I want to create some > indexes to see if this will help as I'm sure it will since there are > none currently. > > Now my question, would it be better to create one index with all columns > in the table -or- a separate index for each column field? I was assuming > the latter, but would the index with all columns be beneficial as well? As previously mentioned, making multicolumn indexes may not be your best bet. If you have an index on (field1, field2, field3) and do a query that doesn't select based on field1, you won't use that index. OTOH, if you always select certain fields for order by / where fieldx= then it might be a good bet. Most importantly, if your database is not initialized to locale=C, then you will need to use varchar_ops operators on it. Otherwise your db won't be able to use your indexes.