Re: [PERFORM] Auto generate number in Postgres-9.1.
On 03/20/2017 03:08 PM, Dinesh Chandra 12108 wrote: But on deletion it's not automatically re-adjusting the id's. Do I need to create trigger for this?? It is possible to do but I advice against adjusting the IDs on DELETE due to to do so safely would require locking the entire table in the trigger. Note that serial columns will also get holes on ROLLBACK. In general I think the right thing to do is accept that your ID columns can get a bit ugly. For example: CREATE TABLE t (id serial); INSERT INTO t DEFAULT VALUES; BEGIN; INSERT INTO t DEFAULT VALUES; ROLLBACK; INSERT INTO t DEFAULT VALUES; Gives us the following data in the table: id 1 3 (2 rows) Andreas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Auto generate number in Postgres-9.1.
On 03/20/2017 02:43 PM, Josh Berkus wrote: If I truncate the same table and then again insert rows should start with 1 in “ID” column. That's not how it works, normally. I'd suggest adding an ON TRUNCATE trigger to the table. Actually that may not be necessary as long as you make sure to use the RESTART IDENTITY option when running TRUNCATE. I would argue that is a cleaner solution than using triggers, if you can get away with it. https://www.postgresql.org/docs/9.6/static/sql-truncate.html Andreas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any advice tuning this query ?
I have a couple of suggestions which should lead to some minor improvements, but in general I am surprised by the huge size of the result set. Is your goal really to get a 43 million row result? When a query returns that many rows usually all possible query plans are more or less bad. 1) You can remove "3" from the group by clause to avoid having to sort that data when we already sort by d.date. 2) If (books, date) is the primary key of dates_per_books we can also safely remove "4" from the group by clause further reducing the length of the keys that we need to sort. 3) For a minor speed up change "coalesce(sum(case when i.invno is not null then 1 else 0 end),0)" to "count(i.invno)". Andreas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Does fsync on/off for wal AND Checkpoint?
On 12/27/2013 04:55 PM, GR Vishwanath wrote: Is fscnc off/on pertain only to writing WAL buffers to disk? Or is that also relevant to writing of dirty buffers to disk (checkpoint/bg)? Curious because in the docs fsync on/off is mentioned under WAL configuration. Further there is a wal_sync_method but not a checkpoint_sync_method. The setting is for all uses of fsync within the PostgreSQL server, so if you turn it off PostgreSQL should never issue fsync. The only exceptions are some utility tools (eg. pg_basebackup) which do not read the configuration file. -- Andreas Karlsson -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Problem with slow query with WHERE conditions with OR clause on primary keys
On 12/11/2013 12:30 AM, Krzysztof Olszewski wrote: select g.gd_index, gd.full_name from gd g join gd_data gd on (g.id_gd = gd.id_gd) where gd.id_gd_data = OR g.id_gd = ; Have you tried writing the query to filter on gd.id_gd rather than g.id_gd? I am not sure if the query planner will realize that it can replace g.id_gd with gd.id_gd in the where clause. select g.gd_index, gd.full_name from gd g join gd_data gd on (g.id_gd = gd.id_gd) where gd.id_gd_data = OR gd.id_gd = ; -- Andreas Karlsson -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
