Re: Views don't seem to use indexes?
Tim Slechta writes: > Why does the planner not use an index when a view is involved? It's not about a "view" ... you'd get the same results if you wrote out the UNION ALL construct in-line as a sub-select. I think you may be shooting yourself in the foot by not making sure that the UNION ALL arms match in data type. You did not show us the definition of pworkspaceobject, but if pworkspaceobject.pobject_name isn't of type text (maybe it's varchar?) then the type mismatch would prevent pushing down a condition on that column. The source code says: * For subqueries using UNION/UNION ALL/INTERSECT/INTERSECT ALL, we can * push quals into each component query, but the quals can only reference * subquery columns that suffer no type coercions in the set operation. * Otherwise there are possible semantic gotchas. I'm too tired to reconstruct an example of the semantic issues... regards, tom lane
Re: Views don't seem to use indexes?
On Wed, Oct 27, 2021 at 7:31 PM Tim Slechta wrote: > > == Point 2. The equivalent query on the VL10N_OBJECT_NAME view executes a > Seq Scan on the underlying pl10n_object_name. Why? > tc=# EXPLAIN ANALYZE select pval_0 from VL10N_OBJECT_NAME where pval_0 = > ''; > Just to confirm and simplify, the question boils down to: Why does: SELECT * FROM view WHERE view.view_column = ?; And view is: CREATE VIEW AS SELECT ..., view_column FROM tbl1 UNION ALL SELECT ..., view_column FROM tbl2 ; Where tbl1 has an index on view_column AND tbl2 does not have an index on view_column Result in a plan where both tb11 and tbl2 are sequentially scanned and the filter applied to the unioned result Instead of a plan where the index lookup rows of tbl1 are supplied to the union and only tbl2 is sequentially scanned ? I don't have an answer to offer up here. I'm pretty sure we do handle predicate pushdown into UNION ALL generally. I'm unclear exactly what the equivalently rewritten query would be in this case - but demonstrating that a query that doesn't use union all applies the index while the direct access of the view doesn't isn't sufficient to narrow down the problem. It can still either be the rule processing or the union processing that is seeming to make a wrong plan choice. That isn't meant to discount the possibility that this case is actually correct - or at least the best we do presently for one or more technical reasons that I'm not familiar with... David J.
Views don't seem to use indexes?
Why does the planner not use an index when a view is involved? 1) A description of what you are trying to achieve and what results you expect. Why don't plans use indexes when views are involved? A similar query on the underlying table leverages the appropriate index. == Point 1. The following query leverages the pipl10n_object_name_1 index. tc=# EXPLAIN ANALYZE select substr(pval_0, 49, 128) from pl10n_object_name where substr(pval_0, 49, 128) = ''; QUERY PLAN -- Bitmap Heap Scan on pl10n_object_name (cost=4.48..32.15 rows=7 width=32) (actual time=0.040..0.040 rows=0 loops=1) Recheck Cond: (substr((pval_0)::text, 49, 128) = ''::text) -> *Bitmap Index Scan on pipl10n_object_name_1* (cost=0.00..4.48 rows=7 width=0) (actual time=0.039..*0.039* rows=0 loops=1) Index Cond: (substr((pval_0)::text, 49, 128) = ''::text) Planning Time: 0.153 ms Execution Time: 0.056 ms (6 rows) == Point 2. The equivalent query on the VL10N_OBJECT_NAME view executes a Seq Scan on the underlying pl10n_object_name. Why? tc=# EXPLAIN ANALYZE select pval_0 from VL10N_OBJECT_NAME where pval_0 = ''; QUERY PLAN -- Subquery Scan on vl10n_object_name (cost=0.00..323818.92 rows=5228 width=32) (actual time=2851.799..2851.801 rows=0 loops=1) Filter: (vl10n_object_name.pval_0 = ''::text) Rows Removed by Filter: 1043308 -> Append (cost=0.00..310749.58 rows=1045547 width=208) (actual time=0.046..2777.167 rows=1043308 loops=1) -> *Seq Scan on pl10n_object_name* (cost=0.00..252460.06 rows=870536 width=175) (actual time=0.046..*2389.282* rows=870645 loops=1) -> Subquery Scan on "*SELECT* 2" (cost=0.00..44356.42 rows=175011 width=175) (actual time=0.019..313.357 rows=172663 loops=1) -> Seq Scan on pworkspaceobject (cost=0.00..42168.79 rows=175011 width=134) (actual time=0.016..291.661 rows=172663 loops=1) Filter: ((pobject_name IS NOT NULL) AND (vla_764_24 = 0)) Rows Removed by Filter: 870629 Planning Time: 0.204 ms Execution Time: 2851.830 ms (11 rows) == Additional Information == == View definition: tc=# \d+ VL10N_OBJECT_NAME View "public.vl10n_object_name" Column| Type | Collation | Nullable | Default | Storage | Description -+---+---+--+-+--+- puid| character varying(15) | | | | extended | locale | text | | | | extended | preference | text | | | | extended | status | text | | | | extended | sequence_no | numeric | | | | main | pval_0 | text | | | | extended | View definition: SELECT pl10n_object_name.puid, substr(pl10n_object_name.pval_0::text, 1, 5) AS locale, substr(pl10n_object_name.pval_0::text, 7, 1) AS preference, substr(pl10n_object_name.pval_0::text, 9, 1) AS status, tc_to_number(substr(pl10n_object_name.pval_0::text, 11, 4)::character varying) AS sequence_no, substr(pl10n_object_name.pval_0::text, 49, 128) AS pval_0 FROM pl10n_object_name UNION ALL SELECT pworkspaceobject.puid, 'NONE'::text AS locale, 'M'::text AS preference, 'M'::text AS status, 0 AS sequence_no, pworkspaceobject.pobject_name AS pval_0 FROM pworkspaceobject WHERE pworkspaceobject.pobject_name IS NOT NULL AND pworkspaceobject.vla_764_24 = 0; == Table definition: tc=# \d+ pl10n_object_name Table "public.pl10n_object_name" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ++---+--+-+--+--+- puid | character varying(15) | | not null | | extended | | pseq | integer| | not null | | plain | | pval_0 | character varying(176) | | | | extended | | Indexes: "pipl10n_object_name" PRIMARY KEY, btree (puid, pseq) DEFERRABLE INITIALLY DEFERRED "pipl10n_object_name_0" btree (pval_0) "pipl10n_object_name_1" btree (substr(pval_0::text, 49, 128)) "pipl10n_object_name_2" btree (upper(substr(pval_0::text, 49, 128))) "pipl10n_object_name_3" btree (substr(pval_0::text, 1, 5)) "pipl10n_object_na
Re: Lock contention high
Hi, On October 27, 2021 2:44:56 PM PDT, Ashkil Dighin wrote: >Hi, >Yes, lock contention reduced with postgresqlv14. >Lock acquire reduced 18% to 10% >10.49 %postgres postgres[.] LWLockAcquire >5.09% postgres postgres[.] _bt_compare > >Is lock contention can be reduced to 0-3%? Probably not, or at least not easily. Because of the atomic instructions the locking also includes some other costs (e.g. cache misses, serializing store buffers,...). There's a good bit we can do to increase the cache efficiency around buffer headers, but it won't get us quite that low I'd guess. >On pg-stat-activity shown LwLock as “BufferCounter” and “WalInsert” Without knowing what proportion they have to each and to non-waiting backends that unfortunately doesn't help that much.. Andres -- Sent from my Android device with K-9 Mail. Please excuse my brevity.
Re: Performance for initial copy when using pg_logical to upgrade Postgres
On Fri, Sep 24, 2021 at 5:02 PM Tim wrote: > I'm currently doing this with a 2.2 TB database. > > Best way IMO is to (UPDATE pg_index SET indisready = false ... ) for non > PK indexes for the largest tables. Then just set it back to indisready = > true after its done and run a REINDEX CONCURRENTLY on the indexes that were > disabled. > > Got about a transfer speed of 100GB per 50 minutes with this method with > consistent results. > Thanks Tim, that has worked great. I'm trying to automate the whole process but I can't see a way of seeing when the initial pglogical copy is complete short of checking the disk space. All I've found is:- select * from pglogical.local_sync_status; sync_kind | sync_subid | sync_nspname | sync_relname | sync_status | sync_statuslsn ---++--+--+-+ d | 1821676733 | | | d | 0/0 (1 row) or xxx=# select * from pg_stat_replication ; -[ RECORD 1 ]+ pid | 3469521 usesysid | 77668435 usename | xxx application_name | _snap client_addr | 10.44.16.83 client_hostname | client_port | 52594 backend_start| 2021-10-27 12:51:17.618734+00 backend_xmin | 221892481 state| startup sent_lsn | write_lsn| flush_lsn| replay_lsn | write_lag| flush_lag| replay_lag | sync_priority| 0 sync_state | async
