view reading information_schema is slow in PostgreSQL 12
I'm facing performance issues migrating from postgres 10 to 12 (also from 11 to 12) even with a new DB. Th performance difference is huge 300ms in pg10 vs 3 minutes in pg12. I have a view that abstracts the data in the database: CREATE OR REPLACE VIEW public.my_constraints AS SELECT lower(tc.constraint_name) AS constraint_name, tc.constraint_type, tc.table_schema, lower(tc.table_name) AS table_name, lower(kcu.column_name) AS column_name, ccu.table_schema AS reference_table_schema, lower(ccu.table_name) AS reference_table_name, lower(ccu.column_name) AS reference_column_name, rc.update_rule, rc.delete_rule FROM information_schema.table_constraints tc LEFT JOIN information_schema.key_column_usage kcu ON tc.constraint_catalog = kcu.constraint_catalog AND tc.constraint_schema = kcu.constraint_schema AND tc.constraint_name = kcu.constraint_name LEFT JOIN information_schema.referential_constraints rc ON tc.constraint_catalog = rc.constraint_catalog AND tc.constraint_schema = rc.constraint_schema AND tc.constraint_name = rc.constraint_name LEFT JOIN information_schema.constraint_column_usage ccu ON rc.unique_constraint_catalog = ccu.constraint_catalog AND rc.unique_constraint_schema = ccu.constraint_schema AND rc.unique_constraint_name = ccu.constraint_name WHERE tc.constraint_catalog = 'my_catalog' AND tc.constraint_schema = 'public' AND tc.constraint_type <> 'CHECK'; The simple query: select * from my_constraints is normal but as soon as I add where constraint_type = 'FOREIGN KEY' it takes a lot of time. I don't have data in my tables at the moment, I have around 600 tables in my schema. I've analyzed the query but can't figure out what's wrong, this is the query with the filter without the view: select * from (SELECT lower(tc.constraint_name) AS constraint_name, tc.constraint_type, tc.table_schema, lower(tc.table_name) AS table_name, lower(kcu.column_name) AS column_name, ccu.table_schema AS reference_table_schema, lower(ccu.table_name) AS reference_table_name, lower(ccu.column_name) AS reference_column_name, rc.update_rule, rc.delete_rule FROM information_schema.table_constraints tc LEFT JOIN information_schema.key_column_usage kcu ON tc.constraint_catalog = kcu.constraint_catalog AND tc.constraint_schema = kcu.constraint_schema AND tc.constraint_name = kcu.constraint_name LEFT JOIN information_schema.referential_constraints rc ON tc.constraint_catalog = rc.constraint_catalog AND tc.constraint_schema = rc.constraint_schema AND tc.constraint_name = rc.constraint_name LEFT JOIN information_schema.constraint_column_usage ccu ON rc.unique_constraint_catalog = ccu.constraint_catalog AND rc.unique_constraint_schema = ccu.constraint_schema AND rc.unique_constraint_name = ccu.constraint_name WHERE tc.constraint_catalog = 'my_catalog' AND tc.constraint_schema = 'public' AND tc.constraint_type <> 'CHECK' ) as a where constraint_type = 'FOREIGN KEY' postgres 10 plan https://explain.depesz.com/s/mEmv postgres 12 plan https://explain.depesz.com/s/lovP -- Sent from: https://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
Re: view reading information_schema is slow in PostgreSQL 12
> view reading information_schema is slow in PostgreSQL 12 Hi, What is the PG version? IF PG < 12.3 THEN maybe related to this ? https://www.postgresql.org/docs/release/12.3/ ( Repair performance regression in information_schema.triggers view ) Imre regrog ezt írta (időpont: 2020. jún. 12., P, 20:26): > I'm facing performance issues migrating from postgres 10 to 12 (also from > 11 > to 12) even with a new DB. > Th performance difference is huge 300ms in pg10 vs 3 minutes in pg12. > > I have a view that abstracts the data in the database: > > CREATE OR REPLACE VIEW public.my_constraints > AS SELECT lower(tc.constraint_name) AS constraint_name, > tc.constraint_type, > tc.table_schema, > lower(tc.table_name) AS table_name, > lower(kcu.column_name) AS column_name, > ccu.table_schema AS reference_table_schema, > lower(ccu.table_name) AS reference_table_name, > lower(ccu.column_name) AS reference_column_name, > rc.update_rule, > rc.delete_rule >FROM information_schema.table_constraints tc > LEFT JOIN information_schema.key_column_usage kcu ON > tc.constraint_catalog = kcu.constraint_catalog AND tc.constraint_schema = > kcu.constraint_schema AND tc.constraint_name = kcu.constraint_name > LEFT JOIN information_schema.referential_constraints rc ON > tc.constraint_catalog = rc.constraint_catalog AND tc.constraint_schema = > rc.constraint_schema AND tc.constraint_name = rc.constraint_name > LEFT JOIN information_schema.constraint_column_usage ccu ON > rc.unique_constraint_catalog = ccu.constraint_catalog AND > rc.unique_constraint_schema = ccu.constraint_schema AND > rc.unique_constraint_name = ccu.constraint_name > WHERE tc.constraint_catalog = 'my_catalog' AND tc.constraint_schema = > 'public' AND tc.constraint_type <> 'CHECK'; > > The simple query: select * from my_constraints is normal but as soon as I > add where constraint_type = 'FOREIGN KEY' it takes a lot of time. > I don't have data in my tables at the moment, I have around 600 tables in > my > schema. > > I've analyzed the query but can't figure out what's wrong, this is the > query > with the filter without the view: > > select * from (SELECT lower(tc.constraint_name) AS constraint_name, > tc.constraint_type, > tc.table_schema, > lower(tc.table_name) AS table_name, > lower(kcu.column_name) AS column_name, > ccu.table_schema AS reference_table_schema, > lower(ccu.table_name) AS reference_table_name, > lower(ccu.column_name) AS reference_column_name, > rc.update_rule, > rc.delete_rule >FROM information_schema.table_constraints tc > LEFT JOIN information_schema.key_column_usage kcu ON > tc.constraint_catalog = kcu.constraint_catalog AND tc.constraint_schema = > kcu.constraint_schema AND tc.constraint_name = kcu.constraint_name > LEFT JOIN information_schema.referential_constraints rc ON > tc.constraint_catalog = rc.constraint_catalog AND tc.constraint_schema = > rc.constraint_schema AND tc.constraint_name = rc.constraint_name > LEFT JOIN information_schema.constraint_column_usage ccu ON > rc.unique_constraint_catalog = ccu.constraint_catalog AND > rc.unique_constraint_schema = ccu.constraint_schema AND > rc.unique_constraint_name = ccu.constraint_name > WHERE tc.constraint_catalog = 'my_catalog' AND tc.constraint_schema = > 'public' AND tc.constraint_type <> 'CHECK' > ) as a > where constraint_type = 'FOREIGN KEY' > > > postgres 10 plan > https://explain.depesz.com/s/mEmv > > postgres 12 plan > https://explain.depesz.com/s/lovP > > > > -- > Sent from: > https://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html > > >
Re: view reading information_schema is slow in PostgreSQL 12
regrog writes:
> I'm facing performance issues migrating from postgres 10 to 12 (also from 11
> to 12) even with a new DB.
> The simple query: select * from my_constraints is normal but as soon as I
> add where constraint_type = 'FOREIGN KEY' it takes a lot of time.
I looked at this a bit. I see what's going on, but I don't see an easy
workaround :-(. The information_schema.table_constraints view contains
a UNION ALL, which in your v10 query produces this part of the plan:
-> Append (cost=0.29..1127.54 rows=316 width=192) (actual
time=0.068..11.116 rows=1839 loops=1)
-> Subquery Scan on "*SELECT* 1" (cost=0.29..226.26
rows=1 width=192) (actual time=0.068..10.952 rows=1839 loops=1)
-> Result (cost=0.29..226.25 rows=1 width=288)
(actual time=0.067..10.707 rows=1839 loops=1)
One-Time Filter:
(((current_database())::information_schema.sql_identifier)::text =
'testzeal'::text)
-> Nested Loop (cost=0.29..226.25 rows=1
width=288) (actual time=0.055..10.454 rows=1839 loops=1)
...
-> Subquery Scan on "*SELECT* 2" (cost=1.44..901.27
rows=315 width=192) (actual time=0.001..0.001 rows=0 loops=1)
-> Result (cost=1.44..898.12 rows=315
width=288) (actual time=0.001..0.001 rows=0 loops=1)
One-Time Filter: 'CHECK'::character
varying)::information_schema.character_data)::text <> 'CHECK'::text) AND
(((current_database())::information_schema.sql_identifier)::text =
'testzeal'::text) AND ((('CHECK' (...)
-> Nested Loop (cost=1.44..898.12
rows=315 width=288) (never executed)
...
The first clause in that "One-Time Filter" arises from your view's
"tc.constraint_type <> 'CHECK'" condition. It's obviously constant-false,
but the v10 planner can't quite prove that because of the domain cast
that's in the way. So the second arm of the UNION doesn't contribute any
actual result rows, but nonetheless it adds 315 rows to the estimated
output of the Append. In v12, this same UNION produces just this:
-> Subquery Scan on "*SELECT* 1" (cost=0.29..199.30 rows=1 width=352)
(actual time=0.382..45.343 rows=1848 loops=1)
-> Result (cost=0.29..199.29 rows=1 width=512) (actual
time=0.381..44.384 rows=1848 loops=1)
One-Time Filter:
(((current_database())::information_schema.sql_identifier)::text =
'testzeal'::text)
-> Nested Loop (cost=0.29..199.28 rows=1 width=257) (actual
time=0.376..40.953 rows=1848 loops=1)
...
The v12 planner is able to see through the domain cast, prove that
'CHECK' <> 'CHECK' is constant false, and thereby toss the entire second
half of the UNION as being a no-op. Great work! Except that now, the
estimated output rowcount is just one row not 316, which causes the
entire shape of the surrounding plan to change, to a form that is pretty
awful when the output rowcount is actually 1800-some. The rowcount
estimates for the two UNION arms were just as lousy in v10, but it quite
accidentally fell into an overall estimate that was at least within an
order of magnitude of reality, allowing it to produce an overall plan
that didn't suck.
To get a decent plan out of v12, the problem is to get it to produce
a better rowcount estimate for the first arm of table_constraints'
UNION. We don't necessarily need it to match the 1800 reality, but
we need it to be more than 1. Unfortunately there's no simple way
to affect that. The core misestimate is here:
-> Seq Scan on pg_constraint c_1 (cost=0.00..192.60 rows=14
width=73) (actual time=0.340..3.962 rows=1848 loops=1)
Filter: ((contype <> ALL ('{t,x}'::"char"[])) AND ((CASE
contype WHEN 'c'::"char" THEN 'CHECK'::text WHEN 'f'::"char" THEN 'FOREIGN
KEY'::text WHEN 'p'::"char" THEN 'PRIMARY KEY'::text WHEN 'u'::"char" THEN
'UNIQUE'::text ELSE NULL::text END)::text <> 'CHECK'::text) AND ((CASE contype
WHEN 'c'::"char" THEN 'CHECK'::text WHEN 'f'::"char" THEN 'FOREIGN KEY'::text
WHEN 'p'::"char" THEN 'PRIMARY KEY'::text WHEN 'u'::"char" THEN 'UNIQUE'::text
ELSE NULL::text END)::text = 'FOREIGN KEY'::text))
Rows Removed by Filter: 1052
I expect you're getting a fairly decent estimate for the "contype <>
ALL" condition, but the planner has no idea what to make of the CASE
construct, so it just falls back to a hard-wired default estimate.
I don't have any good suggestions at the moment. If you had a lot more
tables (hence more rows in pg_constraint) the plan would likely shift
to something tolerable even with the crummy selectivity estimate for the
CASE. But where you are, it's hard. A conceivable workaround is to
drop the "tc.constraint_type <
Re: view reading information_schema is slow in PostgreSQL 12
On Sat, 13 Jun 2020 at 06:26, regrog wrote: > > I'm facing performance issues migrating from postgres 10 to 12 (also from 11 > to 12) even with a new DB. > Th performance difference is huge 300ms in pg10 vs 3 minutes in pg12. This appears to be down to bad statistics that cause pg12 to choose a nested loop plan. The pg12 plan has: -> Hash Join (cost=1281.91..2934.18 rows=68 width=192) (actual time=0.024..21.915 rows=3538 loops=1848)" on the inner side of a nested loop. 21.915 * 1848 loops is 40498.92 ms, so most of the time. This comes down to the difference caused by 04fe805a17, where after that commit we don't bother looking at the NOT NULL constraints in table_constraints. explain select * from (select * from information_schema.table_constraints) c where constraint_type <> 'CHECK'; If you execute the above on both instances, you'll see PG12 does not do an Append. PG10 does. Which results in more rows being estimated and the planner choosing something better than a nested loop join. You could try: SET enable_nestloop TO off; I'm not really sure there's much you could do to improve the statistics on the catalogue tables. Alternatively, you could write a view based directly on the base tables, bypassing information_schema completely. David
Re: view reading information_schema is slow in PostgreSQL 12
On Sat, 13 Jun 2020 at 15:11, Tom Lane wrote: > I expect you're getting a fairly decent estimate for the "contype <> > ALL" condition, but the planner has no idea what to make of the CASE > construct, so it just falls back to a hard-wired default estimate. This feels quite similar to [1]. I wondered if it would be more simple to add some smarts to look a bit deeper into case statements for selectivity estimation purposes. An OpExpr like: CASE c.contype WHEN 'c' THEN 'CHECK' WHEN 'f' THEN 'FOREIGN KEY' WHEN 'p' THEN 'PRIMARY KEY' WHEN 'u' THEN 'UNIQUE' END = 'CHECK'; could be simplified to c.contype = 'c', which we should have statistics for. There'd certainly be case statement forms that couldn't be simplified, but I think this one could. David [1] https://www.postgresql.org/message-id/flat/CAApHDvr%2B6%3D7SZBAtesEavgOQ0ZC03syaRQk19E%2B%2BpiWLopTRbg%40mail.gmail.com#3ec465f343f1204446941df29fc9e715
Re: view reading information_schema is slow in PostgreSQL 12
David Rowley writes: > On Sat, 13 Jun 2020 at 15:11, Tom Lane wrote: >> I expect you're getting a fairly decent estimate for the "contype <> >> ALL" condition, but the planner has no idea what to make of the CASE >> construct, so it just falls back to a hard-wired default estimate. > This feels quite similar to [1]. Yeah, it's the same thing. As I commented in that thread, I'd seen applications of the idea in information_schema views -- it's the same principle of a view exposing a CASE construct that translates a catalog column to what the SQL spec says should be returned, and then the calling query trying to constrain that output. > I wondered if it would be more simple to add some smarts to look a bit > deeper into case statements for selectivity estimation purposes. An > OpExpr like: > CASE c.contype WHEN 'c' THEN 'CHECK' WHEN 'f' THEN 'FOREIGN KEY' WHEN > 'p' THEN 'PRIMARY KEY' WHEN 'u' THEN 'UNIQUE' END = 'CHECK'; Hm. Maybe we could reasonably assume that the equality operators used for such constructs are error-and-side-effect-free, thus dodging the semantic problem I mentioned in the other thread? regards, tom lane
Re: view reading information_schema is slow in PostgreSQL 12
On Fri, Jun 12, 2020 at 11:11:09PM -0400, Tom Lane wrote: > regrog writes: > > I'm facing performance issues migrating from postgres 10 to 12 (also from 11 > > to 12) even with a new DB. > > The simple query: select * from my_constraints is normal but as soon as I > > add where constraint_type = 'FOREIGN KEY' it takes a lot of time. > > I looked at this a bit. I see what's going on, but I don't see an easy > workaround :-(. The information_schema.table_constraints view contains > a UNION ALL, which in your v10 query produces this part of the plan: > To get a decent plan out of v12, the problem is to get it to produce > a better rowcount estimate for the first arm of table_constraints' > UNION. We don't necessarily need it to match the 1800 reality, but > we need it to be more than 1. Unfortunately there's no simple way > to affect that. The core misestimate is here: > I expect you're getting a fairly decent estimate for the "contype <> > ALL" condition, but the planner has no idea what to make of the CASE > construct, so it just falls back to a hard-wired default estimate. > > I don't have any good suggestions at the moment. If you had a lot more > tables (hence more rows in pg_constraint) the plan would likely shift > to something tolerable even with the crummy selectivity estimate for the > CASE. But where you are, it's hard. A conceivable workaround is to > drop the "tc.constraint_type <> 'CHECK'" condition from your view, which > would resurrect that UNION arm and probably get you back to something > similar to the v10 plan. For the purposes of making this work for v12, you might try to look at either a temporary table: CREATE TEMP TABLE constraints AS SELECT * FROM information_schema.table_constraints WHERE constraint_type='FOREIGN KEY'; ANALYZE constraints; SELECT * FROM ... LEFT JOIN constraints LEFT JOIN ... or a CTE (which, if it works, is mostly dumb luck): WITH constraints AS MATERIALIZED (SELECT * FROM information_schema.table_constraints) SELECT * FROM constraints WHERE constraint_type='FOREIGN KEY'; Or make a copy of the system view with hacks for the worst misestimates (like contype<>'c' instead of constraint_type<>'CHECK'). -- Justin
Re: view reading information_schema is slow in PostgreSQL 12
so 13. 6. 2020 v 6:34 odesílatel Justin Pryzby napsal: > On Fri, Jun 12, 2020 at 11:11:09PM -0400, Tom Lane wrote: > > regrog writes: > > > I'm facing performance issues migrating from postgres 10 to 12 (also > from 11 > > > to 12) even with a new DB. > > > The simple query: select * from my_constraints is normal but as soon > as I > > > add where constraint_type = 'FOREIGN KEY' it takes a lot of time. > > > > I looked at this a bit. I see what's going on, but I don't see an easy > > workaround :-(. The information_schema.table_constraints view contains > > a UNION ALL, which in your v10 query produces this part of the plan: > > > To get a decent plan out of v12, the problem is to get it to produce > > a better rowcount estimate for the first arm of table_constraints' > > UNION. We don't necessarily need it to match the 1800 reality, but > > we need it to be more than 1. Unfortunately there's no simple way > > to affect that. The core misestimate is here: > > > I expect you're getting a fairly decent estimate for the "contype <> > > ALL" condition, but the planner has no idea what to make of the CASE > > construct, so it just falls back to a hard-wired default estimate. > > > > I don't have any good suggestions at the moment. If you had a lot more > > tables (hence more rows in pg_constraint) the plan would likely shift > > to something tolerable even with the crummy selectivity estimate for the > > CASE. But where you are, it's hard. A conceivable workaround is to > > drop the "tc.constraint_type <> 'CHECK'" condition from your view, which > > would resurrect that UNION arm and probably get you back to something > > similar to the v10 plan. > > For the purposes of making this work for v12, you might try to look at > either a > temporary table: > > CREATE TEMP TABLE constraints AS SELECT * FROM > information_schema.table_constraints WHERE constraint_type='FOREIGN KEY'; > ANALYZE constraints; > SELECT * FROM ... LEFT JOIN constraints LEFT JOIN ... > > or a CTE (which, if it works, is mostly dumb luck): > WITH constraints AS MATERIALIZED (SELECT * FROM > information_schema.table_constraints) SELECT * FROM constraints WHERE > constraint_type='FOREIGN KEY'; > > Or make a copy of the system view with hacks for the worst misestimates > (like > contype<>'c' instead of constraint_type<>'CHECK'). > Tomas Vondra is working on functional statistics. Can it be the solution of CASE issue? Regards Pavel > > -- > Justin > > >
Re: view reading information_schema is slow in PostgreSQL 12
so 13. 6. 2020 v 7:13 odesílatel Pavel Stehule napsal: > > > so 13. 6. 2020 v 6:34 odesílatel Justin Pryzby > napsal: > >> On Fri, Jun 12, 2020 at 11:11:09PM -0400, Tom Lane wrote: >> > regrog writes: >> > > I'm facing performance issues migrating from postgres 10 to 12 (also >> from 11 >> > > to 12) even with a new DB. >> > > The simple query: select * from my_constraints is normal but as soon >> as I >> > > add where constraint_type = 'FOREIGN KEY' it takes a lot of time. >> > >> > I looked at this a bit. I see what's going on, but I don't see an easy >> > workaround :-(. The information_schema.table_constraints view contains >> > a UNION ALL, which in your v10 query produces this part of the plan: >> >> > To get a decent plan out of v12, the problem is to get it to produce >> > a better rowcount estimate for the first arm of table_constraints' >> > UNION. We don't necessarily need it to match the 1800 reality, but >> > we need it to be more than 1. Unfortunately there's no simple way >> > to affect that. The core misestimate is here: >> >> > I expect you're getting a fairly decent estimate for the "contype <> >> > ALL" condition, but the planner has no idea what to make of the CASE >> > construct, so it just falls back to a hard-wired default estimate. >> > >> > I don't have any good suggestions at the moment. If you had a lot more >> > tables (hence more rows in pg_constraint) the plan would likely shift >> > to something tolerable even with the crummy selectivity estimate for the >> > CASE. But where you are, it's hard. A conceivable workaround is to >> > drop the "tc.constraint_type <> 'CHECK'" condition from your view, which >> > would resurrect that UNION arm and probably get you back to something >> > similar to the v10 plan. >> >> For the purposes of making this work for v12, you might try to look at >> either a >> temporary table: >> >> CREATE TEMP TABLE constraints AS SELECT * FROM >> information_schema.table_constraints WHERE constraint_type='FOREIGN KEY'; >> ANALYZE constraints; >> SELECT * FROM ... LEFT JOIN constraints LEFT JOIN ... >> >> or a CTE (which, if it works, is mostly dumb luck): >> WITH constraints AS MATERIALIZED (SELECT * FROM >> information_schema.table_constraints) SELECT * FROM constraints WHERE >> constraint_type='FOREIGN KEY'; >> >> Or make a copy of the system view with hacks for the worst misestimates >> (like >> contype<>'c' instead of constraint_type<>'CHECK'). >> > > Tomas Vondra is working on functional statistics. Can it be the solution > of CASE issue? > and maybe workaround. Can we use functional index there. It has a statistics. Pavel > Regards > > Pavel > > >> >> -- >> Justin >> >> >>
Re: view reading information_schema is slow in PostgreSQL 12
so 13. 6. 2020 v 7:15 odesílatel Pavel Stehule napsal: > > > so 13. 6. 2020 v 7:13 odesílatel Pavel Stehule > napsal: > >> >> >> so 13. 6. 2020 v 6:34 odesílatel Justin Pryzby >> napsal: >> >>> On Fri, Jun 12, 2020 at 11:11:09PM -0400, Tom Lane wrote: >>> > regrog writes: >>> > > I'm facing performance issues migrating from postgres 10 to 12 (also >>> from 11 >>> > > to 12) even with a new DB. >>> > > The simple query: select * from my_constraints is normal but as soon >>> as I >>> > > add where constraint_type = 'FOREIGN KEY' it takes a lot of time. >>> > >>> > I looked at this a bit. I see what's going on, but I don't see an easy >>> > workaround :-(. The information_schema.table_constraints view contains >>> > a UNION ALL, which in your v10 query produces this part of the plan: >>> >>> > To get a decent plan out of v12, the problem is to get it to produce >>> > a better rowcount estimate for the first arm of table_constraints' >>> > UNION. We don't necessarily need it to match the 1800 reality, but >>> > we need it to be more than 1. Unfortunately there's no simple way >>> > to affect that. The core misestimate is here: >>> >>> > I expect you're getting a fairly decent estimate for the "contype <> >>> > ALL" condition, but the planner has no idea what to make of the CASE >>> > construct, so it just falls back to a hard-wired default estimate. >>> > >>> > I don't have any good suggestions at the moment. If you had a lot more >>> > tables (hence more rows in pg_constraint) the plan would likely shift >>> > to something tolerable even with the crummy selectivity estimate for >>> the >>> > CASE. But where you are, it's hard. A conceivable workaround is to >>> > drop the "tc.constraint_type <> 'CHECK'" condition from your view, >>> which >>> > would resurrect that UNION arm and probably get you back to something >>> > similar to the v10 plan. >>> >>> For the purposes of making this work for v12, you might try to look at >>> either a >>> temporary table: >>> >>> CREATE TEMP TABLE constraints AS SELECT * FROM >>> information_schema.table_constraints WHERE constraint_type='FOREIGN KEY'; >>> ANALYZE constraints; >>> SELECT * FROM ... LEFT JOIN constraints LEFT JOIN ... >>> >>> or a CTE (which, if it works, is mostly dumb luck): >>> WITH constraints AS MATERIALIZED (SELECT * FROM >>> information_schema.table_constraints) SELECT * FROM constraints WHERE >>> constraint_type='FOREIGN KEY'; >>> >>> Or make a copy of the system view with hacks for the worst misestimates >>> (like >>> contype<>'c' instead of constraint_type<>'CHECK'). >>> >> >> Tomas Vondra is working on functional statistics. Can it be the solution >> of CASE issue? >> > > and maybe workaround. Can we use functional index there. It has a > statistics. > create table foo(a int); insert into foo select random()* 3 from generate_series(1,100); create view x as select case when a = 0 then 'Ahoj' when a = 1 then 'nazdar' when a = 2 then 'Hi' end from foo; analyze foo; postgres=# explain analyze select * from x where "case" = 'Ahoj'; ┌┐ │ QUERY PLAN │ ╞╡ │ Gather (cost=1000.00..14273.96 rows=5000 width=32) (actual time=1.265..129.771 rows=166744 loops=1) │ │ Workers Planned: 2 │ │ Workers Launched: 2 │ │ -> Parallel Seq Scan on foo (cost=0.00..12773.96 rows=2083 width=32) (actual time=0.031..63.663 rows=55581 loops=3) │ │ Filter: (CASE WHEN (a = 0) THEN 'Ahoj'::text WHEN (a = 1) THEN 'nazdar'::text WHEN (a = 2) THEN 'Hi'::text ELSE NULL::text END = 'Ahoj'::text) │ │ Rows Removed by Filter: 277752 │ │ Planning Time: 0.286 ms │ │ Execution Time: 137.538 ms │ └┘ (8 rows) create index on foo((CASE WHEN (a = 0) THEN 'Ahoj'::text WHEN (a = 1) THEN 'nazdar'::text WHEN (a = 2) THEN 'Hi'::text ELSE NULL::text END)); analyze foo; postgres=# explain analyze select * from x where "case" = 'Ahoj'; ┌─ │ QUERY PLAN ╞═ │ Bitmap Heap Scan on foo (cost=1862.67..10880.17 rows=167000 width=32) (actual time=16.992..65.300 rows=166744 loops=1) │ Recheck Cond: (CASE WHEN (a = 0) THEN 'Ahoj'::text WHEN (a = 1) THEN 'nazdar'::text WHEN (a = 2) THEN 'Hi'::text ELSE NULL::text END = 'Ahoj'::text) │ Heap Blocks: exact=4425 │ -> Bitmap Index Sca
