4 ms·

I'd be interested to know how often these changes have an effect in real queries. The "Use Limit instead of Unique to implement DISTINCT, when possible" change

by twic 3y ago

I'd be interested to know how often these changes have an effect in real queries. The "Use Limit instead of Unique to implement DISTINCT, when possible" change in particular feels like it would only apply to very silly queries. Do the PostgreSQL developers have any source of information about this?

joachimma 3y ago
Where I used to work we allowed duplicate email addresses in the user table for legacy reasons, but we did not want any new entered in the db, so we ran a "select distinct email from users where email = ?" query before creation of new users. I don't think we had more than a 100 rows with the same email though. Most of the duplicates were test users which could have been removed, but I digress.
Sesse__ 3y ago
The problem is that ORMs have a habit of making very silly queries, and developers insist they cannot write SQL to fix that, because it is somehow impure :-) I doubt this is a very _common_ issue, but I'm not surprised if it shows up every now and then.
- 946789987649 3y ago
  > developers insist they cannot write SQL to fix that, because it is somehow impure :-) Because then they lose many of the benefits of why they used an ORM in the first place. Though I am a big fanboy of JOOQ for exactly this reason.
davidrowley 3y ago
(Author of the blog and that feature here) This one did crop up on the pgsql-hackers mailing list. I very much agree that it's unlikely to apply very often, but the good thing was that detecting when it's possible is as simple as checking if a pointer is NULL. So, it's very simple to detect, most likely does not apply very often, but can provide significant performance increases when it can be applied.
- maximegarcia 3y ago
  That would be nice to also optimize SELECT DISTINCT foo FROM bar. It is usually very poor on big tables and we have to do recursive CTE. This comes a lot with admin builders for filters (<select >).
  - davidrowley 3y ago
    SELECT DISTINCT has seen quite a bit of work over the past few years. As of PG15, SELECT DISTINCT can use parallel query. I imagine that might help for big tables. I assume the recursive CTEs comment is skip scanning using an index and looking for the first value higher than the previously seen value? Certainly skip scans would be nice. There has been some work in this area, but not recently. As far as I recall some other infrastructure needed to go in first to make it easier for the query planner to understand when skip scanning would be useful.
yxhuvud 3y ago
I expect it will have effect fairly often - DISTINCT is something less experienced developers often add to fix their bad queries, and generally the first thing I do when I start to improve performance is to rewrite it to not have to do that. So if these improvements to DISTINCT make it more robust to bad queries, then a lot is gained. It probably won't fix all issues, but any improvements are welcome.

by twic 3y ago

joachimma 3y ago

Where I used to work we allowed duplicate email addresses in the user table for legacy reasons, but we did not want any new entered in the db, so we ran a "select distinct email from users where email = ?" query before creation of new users. I don't think we had more than a 100 rows with the same email though. Most of the duplicates were test users which could have been removed, but I digress.

Sesse__ 3y ago

The problem is that ORMs have a habit of making very silly queries, and developers insist they cannot write SQL to fix that, because it is somehow impure :-) I doubt this is a very _common_ issue, but I'm not surprised if it shows up every now and then.

946789987649 3y ago
> developers insist they cannot write SQL to fix that, because it is somehow impure :-) Because then they lose many of the benefits of why they used an ORM in the first place. Though I am a big fanboy of JOOQ for exactly this reason.

davidrowley 3y ago

(Author of the blog and that feature here) This one did crop up on the pgsql-hackers mailing list. I very much agree that it's unlikely to apply very often, but the good thing was that detecting when it's possible is as simple as checking if a pointer is NULL. So, it's very simple to detect, most likely does not apply very often, but can provide significant performance increases when it can be applied.

maximegarcia 3y ago
That would be nice to also optimize SELECT DISTINCT foo FROM bar. It is usually very poor on big tables and we have to do recursive CTE. This comes a lot with admin builders for filters (<select >).
- davidrowley 3y ago
  SELECT DISTINCT has seen quite a bit of work over the past few years. As of PG15, SELECT DISTINCT can use parallel query. I imagine that might help for big tables. I assume the recursive CTEs comment is skip scanning using an index and looking for the first value higher than the previously seen value? Certainly skip scans would be nice. There has been some work in this area, but not recently. As far as I recall some other infrastructure needed to go in first to make it easier for the query planner to understand when skip scanning would be useful.

yxhuvud 3y ago

I expect it will have effect fairly often - DISTINCT is something less experienced developers often add to fix their bad queries, and generally the first thing I do when I start to improve performance is to rewrite it to not have to do that. So if these improvements to DISTINCT make it more robust to bad queries, then a lot is gained. It probably won't fix all issues, but any improvements are welcome.