Wordpress query on MyISAM tables in MariaDB on ZFS takes too long to execute
Short answer

ZFS, etc, are not the main problems.  (See Comments for a rebuttal on ZFS.)

Use InnoDB, not MyISAM.

Fix the indexes in postmeta, as discussed here:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

Avoid index prefixing.  (The above link discusses that, too.)


(If you must use MyISAM, then the indexes recommended there are not adequate.  Let me know if you need more help.)
Long Answer
Several dimensions to this question:

Version -- MariaDB 10.3 versus other versions of MySQL/MariaDB.  This is probably not significant.

OS -- Linux vs FreeBSD vs Windows -- The OS is just a middleman; all are quite mature and have mostly caught up with each other.

HDD vs SDD -- SDD drives are inherently faster for any access.  Plus they are faster for random accesses due to the lack of a physical &quot;seek&quot;.

FileSystem -- ZFS versus many others.  &quot;Journaling&quot; and other advanced techniques will have some impact on the effective performance.

RAID, and especially &quot;battery-backed write cache&quot; has some impact on reads and possibly a big impact on writes.  This applies across all disk operations in all applications.

InnoDB vs MyISAM -- These differ significantly in locking, caching, the organization of data.  Indexes differ a lot.  In MyISAM, fetching a block of data uses the FS's blocksize and caches it in the OS.  A MyISAM index block is 1KB, which is likely to be smaller than the FS block slide, thereby leading perhaps to an extra level of caching.  For InnoDB, all blocks are 16KB.  The interactions between these MariaDB block size and the FS block is hard to predict.  Peformance at many levels depends on whether the data or index needs to be scanned linearly or probed randomly.

The INDEXes -- Above, I focused on this topic because it is where the main improvement can be had.  In the situation here (accessing WP's postmeta), will have a significant and far-reaching performance impact.


Impact of INDEX

My recommendations include having this composite index: (post_id, meta_key) which is optimal for your slow query.  Furthermore...
When using a secondary or primary index, MyISAM reaches into the data file with a random seek and fetches a &quot;block&quot;, hence performance is dependent on FS and HDD/SSD.
With InnoDB, the PRIMARY KEY is &quot;clustered&quot; with the data, thereby eliminating the extra random fetch.  Some of my recommendations take advantage of this efficiency.
With InnoDB, a secondary index requires a second lookup via the PK.
So, in general, it is beneficial to lay out the data in some clustered manner to improve performance.  Changing the PK to start with post_id, helps in this respect.  Furthermore, InnoDB avoids the random seek that MyISAM requires.
&quot;Index prefixing&quot; (eg, &quot;meta_key(191)&quot;) is virtually useless.  So, part of the performance enhance involves any of about 5 techniques to eliminate prefixing.  I'm pretty sure MariaDB 10.2.2  has already fixed the issue; this would let you remove the (191) from any indexes having it.  Second best is if you can assure that all keys will always be under 191 characters, then changing the definition of meta_key is a simple answer.
All of the above add up to decreasing the 6GB of disk activity, probably significantly.  Sorry, but I cannot say specifically if ZFS has any impact.

The specific Query

Scan the entire posts table.  There is no getting around this.  This will be a linear scan through the &quot;data&quot;.  The engines do this task quite differently, but neither engine has a strong advantage here.
For each post row, reach into postmeta.  This is where the engine and indexes make a significant difference.
Dedup the list of meta_key values -- This may be done in RAM or on disk, at the whim of the Optimizer.  You could change a setting to influence that decision, but it would have the side effect of potentially impacting the performance of all other queries, so I won't discuss it.