Hacker News Reader

Comment by pqb | Hacker News Reader

As other commenter mentioned the BigQuery supports the `SELECT * EXCEPT(column names...) FROM mytable` syntax. Also, it is worth noting the `SELECT COUNT(*) FROM mytable` in BigQuery is faster and cheaper than `SELECT COUNT(always_truish_column) FROM mytable`.

View on HN

Bigquery is surprisingly dumb when it comes to rearranging your query to be able to execute it faster. It seems to entire focus of the bigquery team is being able to parallelize all the work to be done, rather than seeing how they could do less work in the first place. The former is clearly interesting when you bill per Gigabyte of data processed...

I suspect that stems from the fact it doesn't have the pregenerated statistics that other databases have, and therefore there isn't much scope to make a smart query optimizer.

Just to be clear, BigQuery bills per byte scanned. So how much work they have to do on the data is almost irrelevant from a cost perspective.

There are improvements to decrease data scanned, for example, good sort orders and partitioning. BQ supports predicate pushdown for both of these.

This performance optimization is valid for most databases:

COUNT(column) means in reality COUNT(column is NOT NULL), so if the column can have nulls the values need to be checked. But in most databases there is no difference in performance because the query optimized is intelligent enough to check whether the column can have null by definition and switches to COUNT(*) if there can't be nulls. In some databases COUNT(primarykey) is even faster, but these are all optimizations most often not needed, so micro optimizations as the query optimizer is intelligent enough to choose the best logic ;)

I was told years ago that count(1) was faster than count(*) and have been using that blindly and without questioning since (MSSQL user), although I can't remember last time I submitted code that did that. Often write it when examining something in the database though.

In PostgreSQL count(1) is slower since PostgreSQL does not optimize count(constant) to count(*).

EDIT: PostgreSQL might be able to optimize that with LLVM but for short running queries it won't.

These specific tricks do exist for every database. But there is no reason for mssql to not handle count(*) and count(1) the same, they are the same. Maybe they are already the same and it‘s just a very old trick which has been replaced by a better query optimizer.

The second part is due to that count(*) would actually be better written as count() if it was not for the weirdness of the SQL standard.

Count(*) makes sense as you can count(column), count(column1, column2, ...), Count(distinct column), and count(distinct column1, column2,...) Just like in a select.

I don't know of any database that supports multi column count. But even if one did, count(*) does not have the semantics that you would expect from that. If the semantics were equivalent to expanding the column list then count(*) with a single nullable column would return the number of non-null values. But instead it is defined to return the number of rows regardless of context, being equivalent to count() or count(1).

I'm not entirely sure why I thought I've done a count with multiple columns before because it does indeed seem not to be a thing.

Reply on news.ycombinator.com