以前書いたこの記事で limit 付きのデータ本体の取得とマッチした件数の取得を 1 クエリにまとめました
with を使って一時テーブルを作りそこから limit 付きのデータ本体取得と件数取得をしています

with tmp_table as (select * from table where 条件)
select *, (select count(*) from tmp_table) from tmp_table order by id limit 10;

これを見返していて思ったのですが
where が一緒で同じクエリの中なら最適化されて with がなくてもパフォーマンスは同じなのでしたっけ？

select *, (select count(*) from table where 条件) from table where 条件 order by id limit 10;

生の SQL だと長い条件を 2 回も書きたくないですがクエリビルダーを使っていると条件部分が変数に入っていて それを 2 箇所で渡すだけで済みます
with みたいな普段使わないものが出てくるほうが扱いづらくもなるので一緒なら where に 2 回同じのを書くようにしたいです

差はあるのか計測してみます

計測

テーブルはシンプルにこういうのです

create table t (id integer primary key, uid integer);

insert into t (id, uid) values
(1, 1), (2, 1), (3, 2), (4, 2), (5, 1), (6, 2), (7, 1);

postgres=# select * from t;
 id | uid
----+-----
  1 |   1
  2 |   1
  3 |   2
  4 |   2
  5 |   1
  6 |   2
  7 |   1
(7 rows)

クエリは with を使うのと使わないのの 2 種類です

with tt as (select id, uid from t where pg_typeof(pg_sleep(1))::text = 'void' and uid = 1)
select id, (select count(*) from tt) from tt order by id limit 2;

select id,
	(select count(*)
		from t
		where pg_typeof(pg_sleep(1))::text = 'void' and uid = 1)
	from t
	where pg_typeof(pg_sleep(1))::text = 'void' and uid = 1
	order by id
	limit 2;

前回だと適当に遅くしようと文字列の repeat とかしてましたが よりわかりやすくするために今回は pg_sleep にしてます

まずは with を使う方です

postgres=# with tt as (select id, uid from t where pg_typeof(pg_sleep(1))::text = 'void' and uid = 1)
postgres-# select id, (select count(*) from tt) from tt order by id limit 2;
 id | count
----+-------
  1 |     4
  2 |     4
(2 rows)

Time: 4006.525 ms (00:04.007)

4 秒かかっています
uid = 1 で先にフィルタされて残った 4 件に対して 1 秒スリープするので 4 秒ということでしょう
where の処理は一時テーブルを作る段階のみなのでそこから select するときにはスリープが発生してないです

次は where を 2 回書く版です

postgres=# select id,
postgres-#   (select count(*)
postgres(#     from t
postgres(#     where pg_typeof(pg_sleep(1))::text = 'void' and uid = 1)
postgres-#   from t
postgres-#   where pg_typeof(pg_sleep(1))::text = 'void' and uid = 1
postgres-#   order by id
postgres-#   limit 2;
 id | count
----+-------
  1 |     4
  2 |     4
(2 rows)

Time: 8012.055 ms (00:08.012)

倍の 8 秒かかっています
同じ条件の where 間で結果が再利用されたりせずに それぞれで where 句を評価してるようです

カウントの方は全件にチェックが必要ですが データ取得の方は limit があるので 2 件分しかいらないはずです
それなのに全件分 where が実行されているのは order by があるからです
order by を除外すると見つかった順に where 句に一致するか確認して 2 件分で終わるので 6 秒です

postgres=# select id,
postgres-#   (select count(*)
postgres(#     from t
postgres(#     where pg_typeof(pg_sleep(1))::text = 'void' and uid = 1)
postgres-#   from t
postgres-#   where pg_typeof(pg_sleep(1))::text = 'void' and uid = 1
postgres-#   limit 2;
 id | count
----+-------
  1 |     4
  2 |     4
(2 rows)

Time: 6008.881 ms (00:06.009)

正確には uid 検索だとインデックスが使われず seq scan なので全件を対象に where 句の条件に一致するかをチェックして一致したものを並び替えています
インデックスが使われるように uid の代わりに id < 10 みたいな条件を入れた場合は order by があっても 6 秒です

---

確認したバージョン

postgres=# select version();
                                                           version
-----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.4 (Debian 14.4-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)