Reputation
19,930
Top tag
Next privilege 20,000 Rep.
Access 'trusted user' tools
Badges
4 37 70
Impact
~1.5m people reached

Mar
18
comment Date of create sequence, function
Not possible - neither 1) nor 2)
Mar
16
comment How to make sure vacuum is happening in postgres sql db
autovacuum never kicked in your example. It doesn't run immediately after a (bulk) delete - it runs periodically and checks if any tables need vacuuming. If you run vacuum mytable after deleting the rows you should see a difference in the cost and runtime. If you run explain (analyze, verbose, buffers) before running vacuum you will probably see that Postgres reads too many blocks after the bulk delete.
Mar
16
comment PostgreSQL Bloat Warning: Vaccuum does not help
Do you have any sessions that are "idle in transaction"? Open transactions that have touched that table will prevent (auto)vacuum to clean up properly.
Mar
16
comment How to make sure vacuum is happening in postgres sql db
Why do you think the cost should get smaller when you run vacuum? You should show us the complete execution plan (maybe even with explain analyze)
Mar
15
comment Postgres Data Dump problem
I don't think you can have multiple spaces as the delimiter. How did you create this statement? Is that taken directly from a SQL script created by pg_dump? If yes, maybe the tab character got lost somehow in the copy & paste process.
Mar
15
comment Postgres Data Dump problem
@MultiTask: I think copy from stdin is only supported in psql
Mar
15
comment Postgres Data Dump problem
@MultiTask: I guess Navicat doesn't supprt copy ... from stdin
Mar
15
comment Postgres Data Dump problem
@MultiTask: are you using psql or some other SQL client?
Mar
14
comment PostgreSQL not running every update on my script
Sounds like a missing commit somewhere. What are the values in the where clause when you run the update?
Mar
14
comment PostgreSQL not running every update on my script
If an update returns "zero affected rows" this simply means no rows matched your where condition. You will need to edit your post and add your table definitions (as create table statements) and some sample data (as insert into statements) that show the problem
Mar
12
comment Sub-select takes “ages” - EXCEPT much faster
Thanks for the test-setup. On my 9.5.1 installation the not in is about twice as fast as the except solution: not in execution plan and except plan
Mar
12
comment Sub-select takes “ages” - EXCEPT much faster
The first plan shows Index Scan using history_pkey on history (...rows=1389582) the second plan shows Index Scan using history_pkey on history (... rows=2738415). So the second plan expects nearly twice as much rows as the first one. Given that this is the primary key index, it's strange that the same where condition would result is such a drastic difference in the estimates. Do you have an index on history.id?
Mar
11
comment What is the name of the default schema in Oracle?
It's the current user's schema (= the name of the current user)
Mar
11
comment Detect when a CREATE INDEX CONCURRENTLY is finished in PostgreSQL
If I'm not mistaken, concurrently does not mean "in the background". It will still run synchronously - it simply does not take locks on the table to allow concurrent modification to the underlying table. So as soon as your create index is finished, the index is built
Mar
10
comment Is it possible to restore a database from 10.50.4033 in 10.00.1600
Removed the Oracle tag, because those versions clearly aren't Oracle.
Mar
10
comment Truncate a decimal field without rounding in VERTICA
According to the manual, this does exist. Try trunc(total::double, 0)
Mar
9
comment Incorrect Query Result
What is the exact data type definition of the code column?
Mar
7
comment Optimizing “random” choice in PostgreSQL
See here and here and here
Mar
7
comment Optimizing “random” choice in PostgreSQL
I think using 9.5's tablesample could speed this up. Especially together with the tsm_system_rows extension: postgresql.org/docs/current/static/tsm-system-rows.html
Mar
6
comment How to select last row in table?
Based on which sort order is that considered the "last row"?