13,165 reputation
12151
bio website
location Munich, Bavaria
age
visits member for 3 years, 8 months
seen 5 mins ago

Mar
9
comment How large should a database table grow?
How many rows does the table have? And what is the definition of the table? What kind of columns does it contain?
Mar
8
comment Use of integer instead of interval (of one type)
Sounds OK to me.
Mar
6
comment What is the difference between MySQL and PostgreSQL in terms of writing SQL queries?
Other not so obvious things: you can't create a view in MySQL with a derived table in the select and you can't use dynamic SQL in functions.
Mar
5
comment What is the difference between MySQL and PostgreSQL in terms of writing SQL queries?
Postgres has much more advanced SQL features than MySQL (CTEs, Window functions, recursive queries, deferrable constraints and many more), here is an overview sql-workbench.net/dbms_comparison.html
Mar
5
comment Can someone explain why left joining two views in mysql is so slow?
Does this mean MySQL is not able to use indexes on the underlying table when querying a view?
Mar
4
comment Delete millions of rows, fast
In my experience creating a new table with only the data you want to keep is usually much faster than deleting large parts of a table. Of course this only works if your can afford to drop the real table at some point (so the application accessing it needs to be stopped as well most probably)
Mar
4
comment 25M x 25M inner join (postgresql) performance
Indexing won't really help as you do want all the rows from both tables. There is no index that can speed up this query.
Mar
4
comment Handling concurrent updates, inserting if and only if no record already exists
I have no idea if advisory locks could be simulated in H2. The reason why I think SERIALIZABLE works better in Postgres, is that I am not sure that H2 implemented it as efficiently as Postgres: wiki.postgresql.org/wiki/Serializable and drkp.net/papers/ssi-vldb12-slides.pdf
Mar
4
comment Handling concurrent updates, inserting if and only if no record already exists
I think both are workable (although SERIALIZABLE might work better in Postgres). If you are thinking about Postgres, using advisory locks could also help.
Mar
4
comment Debugging why postgres is snowballing with system cpu usage
Could this be a checkpoint spike? 24GB of shared_buffers is quite some thing to be written out to disk.
Mar
3
comment performance difference between < and <=
I would be very surprised if you'd see a difference there. Check the execution plan and you'll see if there is (autotrace mode in SQL*Plus can also help you identify any differences)
Mar
3
comment Postgres vs MySQL Database Maintenance
Running vacuum manually should not be needed on any recent Postgres version - it's definitely not something where you need to have a job running to do it. Simply configuring autovacuum should cover most use-cases (but I do agree: the whole vacuum thing is maybe the only thing I don't really like about Postgres - but in most of the cases it isn't really a problem at all)
Mar
3
comment I have mistakenly deleted Datafiles.
You need to restore your backup.
Mar
3
comment Recommendations for how I should create my table based on data given?
Storing comma separated values is almost always a really bad design. Btw: which DBMS are you using? Postgres? Oracle?
Mar
1
comment Why the table gets deleted by the server in mysql?
V$SQL is a valid table name (sqlfiddle.com/#!2/98730/1) and does not require any quoting (neither MySQL's dreaded backticks nor the standard double quotes). That select looks like it should have been sent to an Oracle database actually (because Oracle does have a dynamic system view that is called V$SQL containing a column named PLAN_HASH_VALUE)
Feb
27
comment Firebird-Time Interval- between 2 Dates
To get the number of days between two days simply subtract them: date '2014-02-27' - date '2001-01-01'
Feb
27
comment Is it completely pointless to use NOT NULL after DATE types?
Of course it's possible: sqlfiddle.com/#!15/a39b6/1
Feb
25
comment Postgres settings reported differently in pgsql
In your question you talk about shared buffers, yet your example shows effective_cache_size.
Feb
24
comment Postgres EXPLAIN ANALYZE behavior
The plan doesn't depend on the DB activity (and you said yourself: you always get the same plan). But just because the plan is the same doesn't mean the execution time will be the same. Think about it: how can the a statement have the same runtime when the system is idle and when the CPU and the harddisk are fully utilized - it's simply not possible.
Feb
24
comment Postgres EXPLAIN ANALYZE behavior
An explain analyze should not depend on the client. What influences the runtime mostly is other stuff going on the in the database. If you have a lot of active sessions when you run the statement once, and then you have no other activity when you run the statement next, it will give you different runtimes. Also even though you claim cache has nothing to do with it, it sure does. The data that you retrieve now could be aged out of the cache in an hour because other data has been cached.