Reset DB stats suggestion pg_stat_reset()
Hi Team, Good Afternoon, We have seen some deadlocks and tempfile count in pg_stat_database view. We are trying to reset the stats. Can we use pg_stat_reset() function to reset these stats without any impact stats of databases. Please advise the process to reset the stats. Thanks. Postggen.
Re: Reset DB stats suggestion pg_stat_reset()
Thanks Adrian. I am aware about the functions. Here need is, can we use this?.or is there any known effects after firing the functions?. Thanks, Postgann. On Sat, Feb 22, 2020 at 10:47 PM Adrian Klaver wrote: > On 2/22/20 12:26 AM, postggen2020 s wrote: > > Hi Team, > > > > Good Afternoon, > > > > We have seen some deadlocks and tempfile count in pg_stat_database view. > > We are trying to reset the stats. > > Can we use pg_stat_reset() function to reset these stats without any > > impact stats of databases. > > Please advise the process to reset the stats. > > See here: > > https://www.postgresql.org/docs/12/monitoring-stats.html > > Table 27.20. Additional Statistics Functions > > It details what your options are. > > > > > Thanks. > > Postggen. > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Suggestion to reduce COPY command output to csv file
Hi Team, Thanks a lot all of you, for providing support peoples like me. Could you please provide a suggestion on COPY command. Environment: DB Version:9.5.15 postgis: Table contain GIS data. I have a table with GIS data, its around 300MB and 2Lacks+ records. I want to export all the records to the CSV file. I can able to export the table data into a CSV file, but the exported CSV file size around 162MB. While I am trying to opening the excel it is taking a long time and also while applying vlookup excel is getting hang. I am using below command : \copy cities TO '/var/lib/pgsql/cities.csv' (DELIMITER ',', FORMAT 'csv', HEADER) I am trying to achieve the following : 1. The exported CSV file should have within 10MB. 2. The excel file should open quickly and able to perform vlookups without any problems(like hang). Could you please provide suggestions on below queries: 1. what I am trying to achieve, is the right assumption?. 2. Can we use this command to use for the above use case?. 3. Is there any tool/extension available for the above use case.? Your inputs are highly appreciated. Regards, Postgadm.
Request to help on GIS Query improvement suggestion.
Hi Team, Thanks for your support. Could someone please suggest on the below query. One of the query which was created on GIS data is taking a long time and even it is not taking the index as well. I have included all the required details for reference. Database Stack: === PostgreSQL : 9.5.15 Postgis: 2.2.7 Table Structure: === ALTER TABLE SCHEMA.TABLE_NAME ADD COLUMN parental_path text; Created Indexes on column parental_path: = CREATE INDEX cable_pair_parental_path_idx ON SCHEMA.TABLE_NAME USING btree (md5(parental_path) COLLATE pg_catalog."default"); CREATE INDEX cable_pair_parental_path_idx_fulltext ON SCHEMA.TABLE_NAME USING gist (parental_path COLLATE pg_catalog."default"); Sample data in "parental_path" column: == 'route--2309421/2951584/3373649/2511322/1915187/2696397/2623291/2420708/2144348/2294454,circuit--88458/88460,sheath--8874' Actual Query: = SELECT seq_no + 1 FROM SCHEMA.TABLE_NAME WHERE (parental_path LIKE '%,sheath--' || cable_seq_id || ',%' OR parental_path LIKE 'sheath--' || cable_seq_id || ',%' OR parental_path LIKE '%,sheath--' || cable_seq_id OR parental_path = 'sheath--' || cable_seq_id) ORDER BY seq_no DESC LIMIT 1; Explain Plan: = Limit (cost=108111.60..108111.61 rows=1 width=4) (actual time=4597.605..4597.605 rows=0 loops=1) Output: ((seq_no + 1)), seq_no Buffers: shared hit=2967 read=69606 dirtied=1 -> Sort (cost=108111.60..108113.09 rows=595 width=4) (actual time=4597.603..4597.603 rows=0 loops=1) Output: ((seq_no + 1)), seq_no Sort Key: TABLE_NAME.seq_no DESC Sort Method: quicksort Memory: 25kB Buffers: shared hit=2967 read=69606 dirtied=1 -> *Seq Scan on SCHEMA.TABLE_NAME (cost=0.00..108108.63 rows=595 width=4) (actual time=4597.595..4597.595 rows=0 loops=1)* Output: (seq_no + 1), seq_no Filter: ((TABLE_NAME.parental_path ~~ '%,sheath--64690,%'::text) OR (TABLE_NAME.parental_path ~~ 'sheath--64690,%'::text) OR (TABLE_NAME.parental_path ~~ '%,sheath--64690'::text) OR (TABLE_NAME.parental_path = 'sheath--64690'::text)) Rows Removed by Filter: 1930188 Buffers: shared hit=2967 read=69606 dirtied=1 Please share your suggestion if I have to change or add new objects to the table etc.. Thanks & Regards, PostgAnn.
