npgsql: Performance Inconsistency with fixed length char types

Steps to reproduce

Please see https://github.com/mintsoft/npgsql-performance-char for a command line application demonstrating the issue

The issue

If a table contains a column of char(n) and you are querying that column as a parameter with npgsql if you don’t specify the type of the input value (i.e. AddWithValue("@name", "rob");) then the performance of query is significantly worse.

i.e. Fast:

slowWithTypeCommand.Parameters.AddWithValue("@name", NpgsqlDbType.Char, "rob");

Slow:

charCommand.Parameters.AddWithValue("@name", "rob");`

It does appear that the rowcount has an effect on this, with 1 row in the table the difference is neglible.

This behaviour completely clobbers performance if using an ORM (such as NHibernate) as they seem to be relying on the type of the input value (“rob”) to drive the dbType.

Further technical details

Npgsql version: 4.0.4 PostgreSQL version: 9.5.x Operating system: Server 2012 R2 / Windows 10

About this issue

Original URL
State: closed
Created 5 years ago
Comments: 24 (23 by maintainers)

Most upvoted comments

@asmundkl I didn’t have a chance to post, I’m going to sort it out this evening. I think you’re right about the versions, it’s definitely something we hit when we upgraded to 3.x

EDIT, the post is here: https://www.postgresql.org/message-id/fdf296a6ed074e63dc6d8d54043fc532%40mintsoft.net

mintsoft on Apr 29, 2019

@mintsoft thanks for investigating this further.

We don’t try to do things in the same way as the JDBC driver - there are many small behavior differences, not to mention that the JDBC API is simply different from ADO.NET. In addition, there’s potential backwards compatibility breakage that would occur if we suddenly changed the default mapping of strings from one type to another…

So I don’t think it makes sense for us to change Npgsql’s behavior here. However, the perf issue should definitely investigated further and probably raised with the PostgreSQL people. I’ll leave this issue open to track that and will do the necessary follow-up.

In the meantime, I’d check whether you can simply change your column type to from varchar to text with your ORM, that would be the ideal solution.

roji on Jan 12, 2019

Understood, please post back what you find. Another option could be to look into an alternate index, or to make the column a text instead of char(n) (that’s probably the simplest option).

roji on Jan 5, 2019

Actually, being an idiot. Changing the code to like:

                var charCommand = new NpgsqlCommand("EXPLAIN ANALYZE SELECT * FROM slow_table WHERE name = @name ;", connection);
                charCommand.Parameters.AddWithValue("@name", "rob");
                var slowQueryPlan = new StringBuilder();
                using (var reader = charCommand.ExecuteReader())
                    while(reader.Read())
                    {
                        var line = reader.GetString(0);
                        slowQueryPlan.AppendLine(line);
                    }

                Console.WriteLine("charCommand :: {0}", charStopwatch.Elapsed.TotalMilliseconds);
                Console.WriteLine(slowQueryPlan.ToString());

outputs the plans which look like :

varcharCommmand :: 8.6363
charCommand :: 143.6909
Seq Scan on slow_table  (cost=0.00..2041.01 rows=500 width=13) (actual time=6.606..140.935 rows=1 loops=1)
  Filter: ((name)::text = 'rob'::text)
  Rows Removed by Filter: 100000
Planning time: 0.201 ms
Execution time: 141.146 ms

slowWithTypeCommand :: 18.2287
Index Scan using slow_table_pkey on slow_table  (cost=0.42..8.44 rows=1 width=13) (actual time=14.345..14.362 rows=1 loops=1)
  Index Cond: (name = 'rob'::bpchar)
Planning time: 0.588 ms
Execution time: 14.475 ms

That makes me wonder, what happens if I force a cast on the right hand side of the query

mintsoft on Jan 4, 2019