Hacker News Reader

Comment by tpetry | Hacker News Reader

These arguments are correct and repeated for many many years. Nevertheless in reality most of the stated reasons have almost no real practicability.

They re correct in an "academic view" but most applications are CRUD and based on an ORM and there are not many columns fetched too much which will make any difference. There are some rare cases where these statements are correct, especially the lob fetching but in these cases most often the queries are switched to manually specifying the columns just for these tables.

What is really needed would be something like SELECT * EXCEPT verylargecolumn FROM mytable ... So in most cases fetching to many columns would be no problem, but if there is really a column which is probelamtic you could easily request to ignore it.

View on HN

It's not jut about large columns.

When fetching from views, selecting unneeded columns can trigger unneeded JOINs.

Assuming no JOINed column is mentioned in the select list (either explicitly or through *), the query optimizer can eliminate the OUTER JOIN, or INNER JOIN when it is known that the referenced row exists (because there is a FOREIGN KEY).

This is mentioned in the article as "Oracle’s join elimination transformation", but (most?) other databases can do it too.

Wouldn’t that only apply if the query or the view features a DISTINCT or GROUP BY, and no COUNT()? Otherwise you’d still need to know how many rows in the other table match the predicate, so you’d still need to do the join, right?

I guess it depends on the join predicate. For a vanilla equi-join, no grouping is required.

Let's say we have the following two tables and a view that joins them:

    CREATE TABLE PARENT (
        PARENT_ID int PRIMARY KEY,
        PARENT_NAME varchar(255)
    );

    CREATE TABLE CHILD (
        CHILD_ID int PRIMARY KEY REFERENCES PARENT,
        CHILD_NAME varchar(255)
    );

    CREATE VIEW PARENT_CHILD
    AS
        SELECT
            *
        FROM
            PARENT
            JOIN CHILD
                ON PARENT_ID = CHILD_ID;

Now if you select any fields from PARENT, the query plan will physically access both PARENT and CHILD. For example:

    SELECT
        *
    FROM
        PARENT_CHILD;

    SELECT
        PARENT_ID,
        PARENT_NAME
    FROM
        PARENT_CHILD;

But the following would physically access only CHILD (because it knows that for any existing CHILD row, the corresponding PARENT row must also exist):

    SELECT
        CHILD_ID,
        CHILD_NAME
    FROM
        PARENT_CHILD;

What I’m suggesting is that the following still has to access CHILD despite only projecting columns from PARENT. If I understand it correctly, not projecting any columns from a joined table doesn’t mean it won’t be accessed, even for vanilla equi-joins.

  SELECT
      PARENT_ID
  FROM
      PARENT_CHILD;

My experience is quite contrary :) I prefer to not use ORM (even if I wouldn’t mind, clients often ask me to work with the DB directly, without ORM), and every selected column matters - in terms of performance and amount of transferred data. Some fields are TEXT/BLOB and sending thousands of them “just because” is not cool.

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`.

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.

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.

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.

This is supported by some databases and DWHs. I work with BigQuery and they do have that syntax

>most applications are CRUD and based on an ORM

Do you actually believe that most CRUD apps use an ORM?

Yes? Most web frameworks do include some form of ORM.

The existence of an ORM in your framework doesn’t mean you necessarily use it. I’ve worked on several projects where the ORM is acknowledged as very handy when you are doing a prototype/poc and for really simple things, but for anything even a little complex you should be writing your own SQL by hand so you know exactly what the db is doing and the cost of it. I personally have come to the conclusion that letting an ORM generate nontrivial SQL for you is actually harder than just writing the SQL yourself, because you have to think “how do I get this higher-level abstraction to generate the actual SQL I want?” and at that point you may as well just write SQL.

I mean, 90% of my application is simple things. I mostly use the Django ORM and even more complicated joins, grouping and subqueries are predictable. I'm curious what ORM people are using that obscures the queries that are being emitted so much. For the 10% where things are complicated I will happily write SQL.

I wouldn't say the ORM is "for prototypes/pocs", but rather it should be the default and raw SQL is "complicated stuff only".

I've become convinced that the best ORM approach is for the ORM to generate code from SQL and not the other way around.

Anecdotally yes, most small to medium sized CRUD apps do use either an ORM or a query builder so abstracted as to be effectively an ORM.

Reply on news.ycombinator.com