Re: SQL - Indexing for performance on uniquness check...
От | Tom Lane |
---|---|
Тема | Re: SQL - Indexing for performance on uniquness check... |
Дата | |
Msg-id | 8589.1090209948@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Re: SQL - Indexing for performance on uniquness check... (Josh Berkus <josh@agliodbs.com>) |
Список | pgsql-novice |
Josh Berkus <josh@agliodbs.com> writes: > Charles, >> Sample query to return non-uniqueness >> SELECT A1, A2, A3, ..., An >> FROM Table >> GROUP BY A1, A2, A3, ..., An >> HAVING Count(*)>1 > In order for it to be even possible to use an index (a hashaggregate > operation, actually) on this table, you'd have to include *all* of the GROUP > BY columns in a single, multi-column index. > However, it would be unlikely for PG to use any kind of an index in the > operation above, because of the number of columns, the unlikelyness of > grouping (i.e. there will only be a minority of rows with count(*) > 1) and > the fact that you're running this against the whole table. So any > kind of an index is liable to be useless. Yeah. If you are not expecting a huge number of groups, I think that it would be more interesting to try a HashAggregate plan than a sort/group plan. For this you need 7.4 or later and a sort_mem setting large enough to cover whatever the planner estimates the hashtable size to be. regards, tom lane
В списке pgsql-novice по дате отправления: