2

I'm trying to determine the performance characteristics of citext. In Microsoft SQL Server, the nvarchar(max) has performance hits when there is excessive memory grants required to use it versus something like a varchar(50). There are other performance hits that come in to play also. I'm trying to determine if citext in PostgreSQL has similar issues. We have a PostgreSQL backed web-app that is horribly slow and I'm wondering if the extensive use of the citext data type is contributing. Any thoughts on this? Thank you!

2

Case insensitive comparison is always costly than case sensitive one. The documentation also cites that in Limitations section. As I understood, the main problem is that you have a performance issue and you cannot locate the culprit(s). I suggest you to use pgBadger or pg_stat_statements to detect slow queries.

  • Will do. Am a newbie to PostgreSQL as you can tell. – JoeDirt Jan 9 '18 at 16:35
  • As an added note, it isn't the case that we are searching for a slow query, it is trying to determine why our PostgreSQL backed website is so much slower than our SQL Server backed website. This, of course, can be due to any number of things. I'm trying to find low-hanging fruit to check and I was hoping that there was some evidence that creating tables where virtually every column is citext is an anti-pattern and why. So far, no such luck. – JoeDirt Jan 9 '18 at 16:46
  • Personally, I never use citext. If I need "search" feature then I use Full Text Search. Maybe your applications are designed by a team with MSSQL backend. – Sahap Asci Jan 11 '18 at 8:11
  • Yes, that is the issue and the same is true from the DB perspective. We've always been a Microsoft shop. From DB, it is disconcerting to find out that clustering (index) and CTEs are not the same. We're learning and appreciate the help. Thanks! – JoeDirt Jan 12 '18 at 12:22
  • AFAIK CTE's are the same. with a_name (columns) AS ( query ) select ...from a_name .. – Sahap Asci Jan 12 '18 at 21:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.