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

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"?
Mar
3
comment PostgreSQL 9.4.6 under RHEL 7 - Extensive logfile because of log_statement_stats - Can't turn off
Do you have the auto_explain module installed? postgresql.org/docs/current/static/auto-explain.html
Mar
3
comment PostgreSQL 9.4.6 under RHEL 7 - Extensive logfile because of log_statement_stats - Can't turn off
What happens if you edit postgresql.conf manually and un-comment those parameters ? (instead of using ALTER TABLE)
Mar
2
comment How to convert the column sum-function-total to dollars in Oracle SQL?
SUM(TO_CHAR(invoice_total)) does not make sense. You can't sum a varchar. You want to_char(sum(invoice_total)).
Feb
29
comment Get the name of the table column referenced by a (auto updatable) view column (PostgreSQL)
Maybe pg_depend contains that information. But it's really ugly to work with
Feb
29
comment Postgres odd behavior with indices
You should add that as an answer and accept it, so that the question is marked as solved
Feb
28
comment Postgres odd behavior with indices
Cross post: stackoverflow.com/q/35658238/330315
Feb
28
comment Postgres odd behavior with indices
Why do you have the limit in the sub-query? It's useless and might fool the optimizer into a bad plan
Feb
25
comment How do I use the result of a CASE statement in a WHERE clause?
Unrelated, but: the parentheses around the CASE .. END are completely unnecessary.
Feb
25
comment TEMP table alternatives for pl/pgsql
The delete statement in your question seems to be incomplete. The condition AND id2=p.id refers to an alias p that is not present. Should that be part of the co-related sub-query?
Feb
24
comment Why would adding LIMIT 200 cause a query to slow down?
Did increasing work_mem get rid of the Sort Method: external merge Disk: step? If not, then the work_mem is still too small.
Feb
24
comment Can't create a view for other user's table despite having select privilege
I think you need to grant the select privilege with "grant option" to the table in order to be able to use it in a view.
Feb
24
comment Why would adding LIMIT 200 cause a query to slow down?
The plan with limit underestimates the rows returned for the core_product table substantially. I guess that's the reason why Postgres chooses the slow nested loop in that case. I am not sure if this is caused by out of date statistics or because of the limit clause. Running analyze core_product might improve this. You can improve the query with the limit clause by increasing the work_mem for the session e.g. set work_mem = '64MB'. That might improve the statement without the limit as well because it might choose a hash join as well instead of the nested loop.
Feb
23
comment Why would adding LIMIT 200 cause a query to slow down?
Post the results of explain (analyze, verbose) instead of the plain analyze
Feb
23
comment Convert a lookup value to foreign key when a query is run
@Caleb: Erwin's answer shows two alternative solutions using a single query.
Feb
23
comment avoid lock and allow two scripts to insert data in one table
@SolracRagnarockradio: that is wrong. You can update, delete, insert with many transactions on the same table without the transactions blocking each other - provided that each transactions deals with different rows. Two inserts will only block each other if the second one is trying to insert the same primary key (or unique key) values as the first one. The same is true for updates: you can have as many concurrent updates on a table as there are unique rows (if each transaction updates a single row)
Feb
23
comment PostgreSQL crash when connection failed
Then you should immediately upgrade to the latest 9.4.6
Feb
23
comment PostgreSQL crash when connection failed
What is your exact Postgres version? There were several Windows related fixes in 9.4.1 and 9.4.6