[teaearlgraycold]

I’m currently learning the basics of this. Currently struggling with multiple similar scenarios where switching from a left to an inner join, or any equivalent, kills performance. But these are aggregation queries so there are only 5 records returned. I could just filter in my app code no problem. But why the hell does adding “where foo.id is not null” in SQL make it O(N*M)??? CTEs are not helping.

[RedShift1]

Have you checked with EXPLAIN ANALYZE VERBOSE?

[teaearlgraycold]

Yup, used a nice EXPLAIN GUI tool as well to try and help.

[Izkata]

Every time stuff like this comes up I wonder how much the people having issues would be willing to share - because every time I've fought with the postgres query planner, it eventually turned out what I wanted to do had massively worse performance* because of something I didn't take into account that postgres did. And each time, once I learned what that thing was, I was able to fix it the right way and get the query plan I was expecting, but also with the performance I was expecting.

* Usually I've been able to force query plans by disabling whole operations for a session, such as disallowing "sort" to make it use an index. The real fix in this case, for example, was to use CLUSTER to re-order data on disk, so the correlation statistic was close to 1 and postgres wanted to use the index instead of table scan + sort.

[sgarland]

> CLUSTER

And here we see the benefit of clustered indices, á la MySQL. Assuming, of course, your PK is k-sortable.

[Izkata]

It may use the same name, but reading https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.h... this doesn't really look like the same thing and wouldn't help here. InnoDB's clustered index (usually on the primary key) is used for fast row lookups by that primary key. It only has an advantage if the primary key is the order you want, but otherwise would have the exact same problem postgres's query planner was protecting me against, but without any way to fix the problem.

[sgarland]

From the same page:

> In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index.

I think that unless you're only doing table scans, or your rows are inserted in no discernible order, you should see a speedup. If the latter though, then yes of course, page jumps are page jumps.

Happy to test this to find out.

[juliusdavies]

I always set this:

SET enable_seqscan = OFF;

[Izkata]

If you hit the case I did, this will make your queries perform worse: The reason postgres insisted on table scan + sort was the random access time jumping around the disk and constantly invalidating the cache when it did an index scan. Using CLUSTER on the table made the index order match the table order so the index scan didn't jump around and there was no random access penalty - it instead worked with the disk cache.

If you're on an SSD and not a spinning disk, or have a lot of memory and can expect it to all be cached, there's a separate setting you can change to adjust the random access penalty - see random_page_cost on https://www.postgresql.org/docs/current/runtime-config-query...

[sgarland]

It shouldn’t. At worst, if id doesn’t have a usable index, a sequential scan of foo, so O(N).