By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
--https://stackoverflow.com/q/79529525/5298879
--Having this situation
--SELECT DISTINCT "FieldName" FROM "TableName" ORDER BY "FieldName" ASC
--I'd like to have the lines containing '%|%' first
--followed by the ones without '%|%'. How to achieve this?
create table "TableName"("FieldName")as values
('aac'),('a|c'),('bad'),('b|d')
,('aac'),('a|c'),('bad'),('b|d');
SELECT 8
--`select distinct` requires that it's corresponding `order by`
--uses the exact fields that are being selected
select distinct "FieldName"
from "TableName"
order by "FieldName" like '%|%' DESC
, "FieldName" ASC;
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 5: order by "FieldName" like '%|%' DESC ^
--you need to nest that in a subquery/CTE
select*from(select distinct "FieldName"
from "TableName") as subquery
order by "FieldName" like '%|%' DESC
, "FieldName" ASC;
| FieldName |
|---|
| a|c |
| b|d |
| aac |
| bad |
SELECT 4
--you can also use a plain `group by`, it extracts `distinct` groups after all
select "FieldName"
from "TableName"
group by "FieldName"
order by strpos("FieldName",'|')>0 desc
, "FieldName";
| FieldName |
|---|
| a|c |
| b|d |
| aac |
| bad |
SELECT 4
--Erwin
--https://stackoverflow.com/a/79530422/5298879
SELECT DISTINCT ON ("FieldName", "FieldName" ~ '\|')
"FieldName"
FROM "TableName"
ORDER BY "FieldName" ~ '\|' DESC, "FieldName";
| FieldName |
|---|
| a|c |
| b|d |
| aac |
| bad |
SELECT 4
select setseed(.42);
insert into "TableName"
select case when .5>random() then md5((random()*1e4)::int::text)
else overlay(md5((random()*1e4)::int::text)
placing '|' from (random()*30)::int+1)
end
from generate_series(1,5e5);
| setseed |
|---|
SELECT 1
INSERT 0 500000
explain analyze verbose
SELECT DISTINCT ON ("FieldName", "FieldName" ~ '\|')
"FieldName"
FROM "TableName"
ORDER BY "FieldName" ~ '\|' DESC, "FieldName";
| QUERY PLAN |
|---|
| Unique (cost=80904.62..85154.96 rows=400 width=33) (actual time=1616.055..2226.937 rows=181022 loops=1) |
| Output: "FieldName", (("FieldName" ~ '|'::text)) |
| -> Sort (cost=80904.62..82321.40 rows=566712 width=33) (actual time=1616.053..2154.592 rows=500008 loops=1) |
| Output: "FieldName", (("FieldName" ~ '|'::text)) |
| Sort Key: (("TableName"."FieldName" ~ '|'::text)) DESC, "TableName"."FieldName" |
| Sort Method: external merge Disk: 21560kB |
| -> Seq Scan on public."TableName" (cost=0.00..11250.90 rows=566712 width=33) (actual time=0.034..342.560 rows=500008 loops=1) |
| Output: "FieldName", ("FieldName" ~ '|'::text) |
| Planning Time: 0.085 ms |
| Execution Time: 2242.085 ms |
EXPLAIN
explain analyze verbose
SELECT DISTINCT ON ("FieldName", strpos("FieldName",'|')>0)
"FieldName"
FROM "TableName"
ORDER BY strpos("FieldName",'|')>0 DESC, "FieldName";
| QUERY PLAN |
|---|
| Unique (cost=72671.36..76421.42 rows=99338 width=34) (actual time=1305.590..1918.848 rows=181022 loops=1) |
| Output: "FieldName", ((strpos("FieldName", '|'::text) > 0)) |
| -> Sort (cost=72671.36..73921.38 rows=500008 width=34) (actual time=1305.587..1844.130 rows=500008 loops=1) |
| Output: "FieldName", ((strpos("FieldName", '|'::text) > 0)) |
| Sort Key: ((strpos("TableName"."FieldName", '|'::text) > 0)) DESC, "TableName"."FieldName" |
| Sort Method: external merge Disk: 21560kB |
| -> Seq Scan on public."TableName" (cost=0.00..11667.12 rows=500008 width=34) (actual time=0.014..116.707 rows=500008 loops=1) |
| Output: "FieldName", (strpos("FieldName", '|'::text) > 0) |
| Planning Time: 0.267 ms |
| Execution Time: 1934.057 ms |
EXPLAIN
explain analyze verbose
select "FieldName"
from "TableName"
group by "FieldName"
order by strpos("FieldName",'|')>0 desc
, "FieldName";
| QUERY PLAN |
|---|
| Sort (cost=15036.33..15160.50 rows=49669 width=34) (actual time=816.487..1047.356 rows=181022 loops=1) |
| Output: "FieldName", ((strpos("FieldName", '|'::text) > 0)) |
| Sort Key: ((strpos("TableName"."FieldName", '|'::text) > 0)) DESC, "TableName"."FieldName" |
| Sort Method: external merge Disk: 7808kB |
| -> HashAggregate (cost=10417.10..11162.14 rows=49669 width=34) (actual time=228.974..341.050 rows=181022 loops=1) |
| Output: "FieldName", (strpos("FieldName", '|'::text) > 0) |
| Group Key: "TableName"."FieldName" |
| Batches: 5 Memory Usage: 8241kB Disk Usage: 11096kB |
| -> Seq Scan on public."TableName" (cost=0.00..9167.08 rows=500008 width=33) (actual time=0.014..43.089 rows=500008 loops=1) |
| Output: "FieldName" |
| Planning Time: 0.083 ms |
| Execution Time: 1061.786 ms |
EXPLAIN
explain analyze verbose
select*from(select distinct "FieldName"
from "TableName") as subquery
order by strpos("FieldName",'|')>0 DESC
, "FieldName" ASC;
| QUERY PLAN |
|---|
| Sort (cost=15533.02..15657.19 rows=49669 width=34) (actual time=842.458..1075.540 rows=181022 loops=1) |
| Output: subquery."FieldName", ((strpos(subquery."FieldName", '|'::text) > 0)) |
| Sort Key: ((strpos(subquery."FieldName", '|'::text) > 0)) DESC, subquery."FieldName" |
| Sort Method: external merge Disk: 7808kB |
| -> Subquery Scan on subquery (cost=10417.10..11658.83 rows=49669 width=34) (actual time=233.340..355.773 rows=181022 loops=1) |
| Output: subquery."FieldName", (strpos(subquery."FieldName", '|'::text) > 0) |
| -> HashAggregate (cost=10417.10..10913.79 rows=49669 width=33) (actual time=233.336..318.000 rows=181022 loops=1) |
| Output: "TableName"."FieldName" |
| Group Key: "TableName"."FieldName" |
| Batches: 5 Memory Usage: 8241kB Disk Usage: 11096kB |
| -> Seq Scan on public."TableName" (cost=0.00..9167.08 rows=500008 width=33) (actual time=0.013..40.086 rows=500008 loops=1) |
| Output: "TableName"."FieldName" |
| Planning Time: 0.092 ms |
| Execution Time: 1089.258 ms |
EXPLAIN