sql: statement with parameters much slower than with string concatenation

[sahlex]

Cockroach version:

Build Tag:    v1.1.4
Build Time:   2018/01/08 17:32:42
Distribution: CCL
Platform:     linux amd64
Go Version:   go1.8.3
C Compiler:   gcc 6.3.0
Build SHA-1:  b794b52cbfffa2340cdaabf1c33be716ebde1db4
Build Type:   release-gnu

I'm using hibernate (5.2.10) together with cockroachdb for a proof-of-concept. While re-writing some queries from mysql to postgres/crdb I noticed a weird behavior. While calling crdb with a native query which I optimized in DBeaver (takes about 20ms) I used parameters in the query using hibernate like this:

    NativeQuery<Right> query =
            session.createNativeQuery("select ri.pkrightid, ri.subject "
                    + " from rights ri "
                    + " where ri.pkrightid in ( "
                    + "	select rr.fkrightid "
                    + "	from role_rights rr "
                    + "	where rr.fkroleid in ( "
                    + "	    select distinct ur.fkroleid from user_role ur "
                    + "	    where ur.fkuserid = :userid "
                    + "	    intersect "
                    + "	    SELECT distinct tr.fkroleid FROM tenant_role tr  "
                    + "	    WHERE tr.fkTenantId = :tenantid "
                    + "	) "
                    + ")", Right.class);
    query.setReadOnly(true);
    query.setParameter("userid", userid);
    query.setParameter("tenantid", tenantid);
    List<Right> rights = query.list();

This query took almost 5 seconds! However, when I put the parameters directly into the statement using string concatenation it's fast again.

    NativeQuery<Right> query =
            session.createNativeQuery("select ri.pkrightid, ri.subject "
                    + " from rights ri "
                    + " where ri.pkrightid in ( "
                    + "	select rr.fkrightid "
                    + "	from role_rights rr "
                    + "	where rr.fkroleid in ( "
                    + "	    select distinct ur.fkroleid from user_role ur "
                    + "	    where ur.fkuserid = " + userid
                    + "	    intersect "
                    + "	    SELECT distinct tr.fkroleid FROM tenant_role tr  "
                    + "	    WHERE tr.fkTenantId = " + tenantid
                    + "	) "
                    + ")", Right.class);
    query.setReadOnly(true);
    List<Right> rights = query.list();

I would have expected the query with the parameters to take the same time (approx) to what I observed in DBeaver.

See also discussion in https://forum.cockroachlabs.com/t/prepared-statement-slower-than-string-concatenated-query/1280/3

[jordanlewis]

Hi @sahlex, thanks for the report. If possible, could you please share the relevant portion of your database schema? Other useful output would be running EXPLAIN (verbose) on your query (with the interpolated values, not the placeholders).

[sahlex]

Hi @jordanlewis . Please find attached the relevant part of the schema and the query plan.

Level |Type     |Field      |Description                                                                                                                                                                                                                                         |Columns                                        |Ordering |
------|---------|-----------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|-----------------------------------------------|---------|
0     |limit    |           |                                                                                                                                                                                                                                                    |(pkrightid, subject)                           |         |
0     |         |count      |10000000                                                                                                                                                                                                                                            |                                               |         |
1     |scan     |           |                                                                                                                                                                                                                                                    |(pkrightid, subject)                           |         |
1     |         |table      |rights@primary                                                                                                                                                                                                                                      |                                               |         |
1     |         |spans      |ALL                                                                                                                                                                                                                                                 |                                               |         |
1     |         |filter     |pkrightid IN (SELECT rr.fkrightid FROM role_rights AS rr WHERE rr.fkroleid IN (SELECT DISTINCT ur.fkroleid FROM user_role AS ur WHERE ur.fkuserid = 123456 INTERSECT SELECT DISTINCT tr.fkroleid FROM tenant_role AS tr WHERE tr.fktenantid = 100)) |                                               |         |
1     |         |subqueries |1                                                                                                                                                                                                                                                   |                                               |         |
2     |render   |           |                                                                                                                                                                                                                                                    |(fkrightid)                                    |         |
2     |         |render 0   |rr.fkrightid                                                                                                                                                                                                                                        |                                               |         |
3     |scan     |           |                                                                                                                                                                                                                                                    |(fkroleid, fkrightid)                          |         |
3     |         |table      |role_rights@primary                                                                                                                                                                                                                                 |                                               |         |
3     |         |spans      |ALL                                                                                                                                                                                                                                                 |                                               |         |
3     |         |filter     |fkroleid IN (SELECT DISTINCT ur.fkroleid FROM user_role AS ur WHERE ur.fkuserid = 123456 INTERSECT SELECT DISTINCT tr.fkroleid FROM tenant_role AS tr WHERE tr.fktenantid = 100)                                                                    |                                               |         |
3     |         |subqueries |1                                                                                                                                                                                                                                                   |                                               |         |
4     |union    |           |                                                                                                                                                                                                                                                    |(fkroleid)                                     |         |
5     |distinct |           |                                                                                                                                                                                                                                                    |(fkroleid)                                     |         |
6     |render   |           |                                                                                                                                                                                                                                                    |(fkroleid)                                     |         |
6     |         |render 0   |ur.fkroleid                                                                                                                                                                                                                                         |                                               |         |
7     |scan     |           |                                                                                                                                                                                                                                                    |(fkuserid[omitted], fkroleid, active[omitted]) |         |
7     |         |table      |user_role@active_user_role                                                                                                                                                                                                                          |                                               |         |
7     |         |spans      |/123456-/123457                                                                                                                                                                                                                                     |                                               |         |
5     |distinct |           |                                                                                                                                                                                                                                                    |(fkroleid)                                     |         |
6     |render   |           |                                                                                                                                                                                                                                                    |(fkroleid)                                     |         |
6     |         |render 0   |tr.fkroleid                                                                                                                                                                                                                                         |                                               |         |
7     |scan     |           |                                                                                                                                                                                                                                                    |(fkroleid, fktenantid[omitted])                |         |
7     |         |table      |tenant_role@tenant_role_auto_index_fk_fktenantid_ref_tenant                                                                                                                                                                                         |                                               |         |
7     |         |spans      |/100-/101                                                                                                                                                                                                                                           |                                               |         |

reduced-db-schema.txt

[jordanlewis]

Thanks. Taking a look at this.

[jordanlewis]

I notice that rights and role_rights are getting full table scans - are those small tables?

[sahlex]

select count(1) from rights;
1154
select count(1) from role_rights;
3000
select count(1) from tenant_role;
233952
select count(1) from user_role;
1743153

[jordanlewis]

Closing this due to inactivity (on our part). That being said, since Jan 2018 we've implemented a whole new SQL planner, a cost-based optimizer, that likely doesn't suffer from the problem that we were talking about here (as I'm guessing the issue here was a planner one).

Please feel free to re-open this if you still see this behavior on a newer version of CockroachDB, and sorry we never got to it.