Reputation
11,907
Next tag badge:
733/1000 score
223/200 answers
Badges
7 32 56
Newest
 Synonymizer
Impact
~596k people reached

Apr
16
comment PostgreSQL: raise error if planner needs to do an sequence scan
@guettli SELECT count(1) FROM thing WHERE t_created >= now() - interval '1 year';
Apr
16
comment PostgreSQL: raise error if planner needs to do an sequence scan
I am not sure if this does not give you more work than necessary. When you carpet bomb the DB with indexes based on assumptions about their necessity, you realize later that many of these are unused. (There are obvious cases where an index will be needed, but those are usually known already at physical data modelling time.)
Apr
15
comment FDW, replication and CREATE SERVER - will it work?
I am not sure from the quesiton, but if the FDW connects to a separate cluster located on the host of the master, it could work (and possibly even make sense).
Apr
14
comment The cost of left out join versus a select
There is absolutely no problem with a LEFT [OUTER] JOIN, it's a common myth, I guess. Read a bit about the N+1 issue, too: stackoverflow.com/questions/97197/what-is-the-n1-selects-issue
Apr
14
comment Match all in a full text search
That sounds like a very nice performance sink :) On the other hand, I could not figure out a good solution for you.
Apr
12
comment Are file locations stored in PostreSQL catalog?
SELECT current_setting('data_directory');
Apr
10
comment Match all in a full text search
Why would you do that?
Apr
8
comment 'ERROR: text search dictionary “unaccent” does not exist' during CREATE INDEX?
I've tried to replicate your issue but the index creation worked.
Apr
4
comment How to temporarily disable foreign keys in Amazon RDS PostgreSQL?
Oh yes, this way it won't work. But how about dropping and recreating the FK constraints?
Apr
3
comment How to temporarily disable foreign keys in Amazon RDS PostgreSQL?
How do you disable FKs now? Why do you expect it to be different on RDS? Also, why not try yourself?
Apr
2
comment Slow query over 10mil rows with group by on indexed column
Well, checking the numbers, the sequential scan returns 9935732 rows, with the filter excluding the remaining 4261215. This means that 2/3 of your table matches the WHERE condition - I would not expect any index helping much on this.
Apr
1
comment Restoring plain-text pg_dump with psql and --disable-triggers
All this means that some data has been updated, right? Try to update them back, using a temp table where you copy the original data.
Mar
31
comment Best way to drop / recreate a table that has dependent views?
I'd say the easiest way to do this is what I shortly described above. You could also attempt scripting the views programmatically, using the different catalogs and system information functions, dropping and recreating the table and then running the script with the views. BTW, what do you do with the existing data?
Mar
31
comment Best way to drop / recreate a table that has dependent views?
I'd strongly suggest revising your design then. Relying on the order of columns is hardly ever a good practice. I admit that it is nicer to have consistency, but one has to keep an eye on the costs of maintaining the nicety. The only case when I really see an advantage in changing the column order is when you can win a few bytes per row with aligning the columns better - as said, in huge tables.
Mar
31
comment Best way to drop / recreate a table that has dependent views?
There is no such possibility. What you could possibly do is to create the extended table anew in a separate schema, then all the dependent views, and finally swap in the new stuff and out the old. By the way, what's your reason for adding a new column in the middle of the table? There are cases when the column order is important, typically when the row number approaches the billion(s).
Mar
31
comment Restoring plain-text pg_dump with psql and --disable-triggers
You can go and edit your dump in any editor. Just prepend the COPY with an ALTER TABLE table DISABLE TRIGGER ALL and reenable these at the end.
Mar
30
comment Have: Postgres database files. Need: Functioning database
Interesting, I see postgresql-9.1 in my output of apt-cache search postgresql...
Mar
30
comment SQL update multiple columns matching a pattern
postgresql.org/docs/9.4/static/sql-update.html
Mar
30
comment What will be best (for performance) transaction isolation level?
Why are you worried about the performance in any given isolation level? BTW, is your ORM prepared to retry every transaction that would be the victim of a serialization error?
Mar
27
comment Unique Index much bigger than the table itself
@IvanDeSousaPaz the index is organized like a tree, and (provided that the order of columns in its definition makes sense, for example follow the most-selective-first approach) can significantly reduce the number of pages to be visited. Look at this page to have an idea how. And yes, you need the index for maintaining uniqueness.