6 ms·
As other commenter mentioned the BigQuery supports the `SELECT * EXCEPT(column names...) FROM mytable` syntax. Also, it is worth noting the `SELECT COUNT(*) FRO
by pqb 6y ago
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`.
- tpetry 6y agoThis 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 ;)
- Zobat 6y agoI 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.
- jeltz 6y agoIn 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.
- tpetry 6y agoThese 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.
- londons_explore 6y agoBigquery 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.
- deleted 6y ago[deleted]
- _dark_matter_ 6y agoJust 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.
- jeltz 6y agoThe 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.
- jimktrains2 6y agoCount(*) makes sense as you can count(column), count(column1, column2, ...), Count(distinct column), and count(distinct column1, column2,...) Just like in a select.
- ants_a 6y agoI 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).
- jimktrains2 6y agoI'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.
- deleted 6y ago[deleted]
- deleted 6y ago[deleted]