getCount(). Perfs issues for multi columns primary key

[jeromeSH26]

Issue type:

[X ] question

Database system/driver:
[X ] postgres

Version 0.2.18

I'm facing a perf issue when running getCount()
Why when counting all records of a query, queryBuilder creates a SQL query that concatenates the columns that are part of the primary key ?

Typescript :

const query: SelectQueryBuilder<TEntity> = dbConnection
					.getRepository<TEntity>(entity)
					.createQueryBuilder(entity.name)
					.where(`"${entity.name}"."${id}"= :val`, {
						val: value.toLowerCase(),
					});

				const totalRecords: number = await query.getCount();

generated sql

SELECT COUNT(DISTINCT(
	CONCAT(
		"u"."a", 
		"u"."b",
		"u"."c")
	)
) as "cnt" FROM "sc"."tbl" "u"

That is destroying the performances for a table of 140K records.
Creating an index on CONCAT using is not efficient, hard to maintain with 100 of tables, knowing that for some tables, some primay comumns are timestamp...

Is there a way or a parameter to set to avoid the concat and just running a kind of (select count(1) FROM "sc"."tbl" "u") ?

Rgds

[Destreyf]

@jeromeSH26

I haven't used any multi-column keys, can you try this?

const query: SelectQueryBuilder<TEntity> = dbConnection
					.getRepository<TEntity>(entity)
					.createQueryBuilder(entity.name)
					.select("COUNT(*)", "count")
					.where(`"${entity.name}"."${id}"= :val`, {
						val: value.toLowerCase(),
					});

const totalRecords: number = await query.getRawOne().then(r => r.count);

This is how i perform my queries, but i'm also using it in a mixed scenario of count/sum/avg so i use the getOneRaw quite a bit.

[jeromeSH26]

Hi Chris,
thanks for your feedback
I tried your solution and that runs 4.5 faster than the native getCount() (147ms Vs 680ms). Il will go with that solution, since I'm using generic resolvers for typegraphql, so the modification to access DB is easy to maintain, as this is centralized in a single function.
However it seems to me more a workaround as a solution. I would expect the native getCount() to be optimizable. But with a COUNT(DISTINCT(CONCAT(....))) query, difficult to optimize as it impacts the design of the DB (need to add specific BTREE indexes) just for a count..

Will close the issue later on, in case some team guys designing typeorm (which is a fantastic tool btw) want to give us some clues

Rgds

This is how I have refactored the query :

const rootQuery = dbConnection
	.getRepository<TEntity>(entity)
	.createQueryBuilder(entity.name);

const countQuery = rootQuery.select("COUNT(1)", "cnt");
const { cnt }: { cnt: number } = await countQuery.getRawOne();

let query = rootQuery
	.cache(withCache)
	.skip(start)
	.take(nbRecords);

	const records: TEntity[] = await query.getMany();

[Destreyf]

@jeromeSH26 i 100% agree that the getCount function should be flexible/adjustable, unfortunately i'm not a maintainer on this project.

[jeromeSH26]

Yep, that's why I leave this question opened for a while

[dsbert]

Note this also causes incorrect counts to be returned.

Here is an example.

You have two number columns set as primary keys - field1 and field2.

The following two rows will return the incorrect count.

{
	field1: 11,
	field2: 101
	// concat(field1, field2) = '11101'
},
{
	field1: 1,
	field2: 1101
	// concat(field1, field2) = '11101'
},

Count should be 2, but returns 1.

[imnotjames]

Is there a way or a parameter to set to avoid the concat and just running a kind of (select count(1) FROM "sc"."tbl" "u") ?

In cases with joins this won't work. If there are no joins it's possible to be a performance improvement we could apply but we'd need a number of tests to validate behavior.

[imnotjames]

Can you confirm that #6870 fixes the issues you're seeing?