From: | Markus Demleitner <msdemlei(at)ari(dot)uni-heidelberg(dot)de> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | SELECT DISTINCT <constant> scans the table? |
Date: | 2021-12-16 11:00:14 |
Message-ID: | 20211216110014.27xcbjfs2rkpjz7t@victor |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Dear list,
This feels like a FAQ, but neither the postgres docs nor web searches
got me a single step towards some sort of insight.
Maximally stripped down, my problem is that
select distinct 300 from <bigtable>
seqscans <bigtable> (at least in PostgreSQL 11.14). To me, it seems
obvious that this ought be be just one row containing 300 once
Postgres has established that <bigtable> is nonempty.
Why do things not work like this? Am I missing something major?
The reason I'm interested in this is of course a bit more involved.
I have a view that looks somewhat like this:
CREATE VIEW a_view AS (
SELECT 'abc' as coll, ...
FROM table1
UNION
SELECT 'def' as coll, ...
FROM table2
UNION
SELECT coll, ...
FROM table3
...)
and so on for perhaps 50 tables; where, as for table3 in this
example, the coll column is not simply a constant, there is an index
on the source column (these then are actually fast).
I'd now like to be able to say
SELECT DISTINCT coll FROM a_view
-- which takes forever once some of the tables involved are
sufficiently large.
I'd assume in an ideal world the query would be essentally
instantaneous -- merging the constants and doing a few index scans
for the tables that have non-constant coll. In reality, it's not.
The tables with constant coll are all completely scanned, either
sequentially or index-only.
Assuming this is the expected behaviour: Is there perhaps some trick
I could use to make postgres use the information that there are
constants in the select clauses?
Thanks,
Markus
From | Date | Subject | |
---|---|---|---|
Next Message | Bryn Llewellyn | 2021-12-16 19:21:56 | Re: Why can't I have a "language sql" anonymous block? |
Previous Message | Matt Magoffin | 2021-12-16 02:11:12 | Re: Properly handling aggregate in nested function call |