Suppose you have the followinguserstable in PostgreSQL.
| id | name | |
|---|---|---|
| 1 | Test Taro 1 | [email protected] |
| 2 | Test Taro 2 | [email protected] |
| 3 | Test Taro 3 | [email protected] |
| 4 | Test Taro 4 | [email protected] |
| 5 | Test Taro 5 | [email protected] |
| 6 | Test Taro 6 | [email protected] |
Emails are duplicates, so I want to find out the number of unique emails.
Keep it simple
SELECT COUNT (DISTINCT email) FROM users;
It's fine, but this is very slow.
Can you somehow speed it up?
I tried usinggroup by, but the speed was not improved.
SELECT email FROM users group by email;
EXISTS clause can be used to increase the speed, but it is not related to other tables, it will be completed in theuserstable, so it seems that it can not be used was.
I want to speed up something other thanDISTINCT. Is there any good idea?
Can it be faster than this if it is so simple?
I'm sorry, the question was a little fluffy, so let me add it.
I want to know the fastest query that satisfies the requirement ofI want to check the number of unique emails in the above tablewithout considering Index etc. That is the question.
When there are many records, it is clear thatSELECT COUNT (distinct email) FROM usersis slower thanSELECT COUNT (email) FROM users. (distinct email) FROM usersI want to know a query that satisfies the requirement thatI want to find out the number of unique emailsearlier.
If it is not simply, I would like to take another approach (index review etc.).
If it is still better to have an explain result, add it.
-
Answer # 1
-
Answer # 2
You can create an index.
11.2. Types of indexes
When searching and narrowing down emails,
An appropriate index design that considers index performance and functions should be performed.
It seems to talk.I imagine that this is a non-production development situation,
That's the situation without an index
I think this is a great opportunity to compare the execution plans by EXPLAIN.
If there is no index, I think it will take time with FULL SCAN.
Related articles
- Using arrays to improve performance in PostgreSQL
- A tip to improve PostgreSQL performance
- Five tips for Instagram to improve PostgreSQL performance
- i want to improve search speed in postgresql with multiple columns and multiple keywords
- i want to improve the processing speed when throwing a select statement to the postgresql db placed on the external network
- postgresql - glassfish garbled characters do not improve and db connection is not possible
- [postgresql]: i want to improve a large amount of insert processing and move data between tables at high speed
- if postgresql distinct and null are used, an error occurs due to "type difference"
- postgresql - a5: how to improve sql mk-2 csv import speed?
- postgresql - i want to aggregate ids with a start date by day until now
- postgresql - simultaneous display of the count number and its original column
- postgresql - i want to derive inventory from three tables
- postgresql - [sql] i want to join two tables
- mysql - i want to know how to write sql to get a list of users who are not yet associated with a particular article
- postgresql doesn't start
- postgresql - i want to modify the table data and add columns
- [postgresql] -u of createuser command
- postgresql - i want to return 0 when the count is 0
- about when inputting postgresql initial data with docker
Setting an appropriate index is often effective in improving performance. Of course, I am not your boss, so I will not force you to add an index.