network profile
| bio | website | |
|---|---|---|
| location | ||
| age | ||
| visits | member for | 2 years |
| seen | May 3 at 16:28 | |
| stats | profile views | 23 |
Available for hire for SQL consulting (ddebernardy at yahoo dot com).
|
2d |
comment |
PostgreSQL group roles This grants write perms to the tables, which isn't what OP wants. Fwiw in addition, I was thinking the same when writing my own answer, but testing revealed a PG bug (in 9.2 anyway). Running alter default privileges in schema foo grant select on tables to public; is, per docs, supposed to grant select on all tables in schema foo to public; automatically on new tables (i.e. what OP wants), but testing revealed that you still need to run that query manually (i.e. what OP is currently doing) for some reason. |
|
2d |
comment |
PostgreSQL group roles After playing around with it, it most definitely looks like there's a bug or three in Postgres... |
|
May 3 |
comment |
InnoDB errors after having deleted “ibdata1” Should go to DBA... |
|
May 3 |
comment |
Can't connect to the postgres server ls: /tmp/.s.PGSQL.5432: No such file or directory (Or the new one might not be running yet.) |
|
May 3 |
comment |
Can't connect to the postgres server ls: /tmp/.s.PGSQL.5432: No such file or directory Restart your Mac. The older version of Postgres (the one that's installed by OSX) might still be running. |
|
May 2 |
comment |
Database design - People and Organisations I'm not saying it's impossible. Merely that it's thorny. And that in practice there's very little value in doing so "properly" considering the joins, validation issues, and UI/UX involved, vs something that would actually cover 99% of cases with a conveniently well known UI, aka Outlook. |
|
May 2 |
comment |
Database design - People and Organisations One big example would be when you normalize the company. If you toss in a link to a screen with an edit button when you click it, you may very well end up with the secretary screw up I mention further up. One alternative, which is a convenience textfield to rename the company, amounts to the same. The last alternative is to have a dumb text field with no ties to the table at all (just like in Outlook), and to tie things back up in a subsequent (or ajax) screen if there is actual need for normalization. |
|
May 2 |
answered | Database design - People and Organisations |
|
May 20 |
awarded | Yearling |
|
Jul 26 |
answered | ts_rank massively slows my query, how can I improve performance? |
|
Jul 4 |
answered | Postgresql row-level encryption |
|
Jun 28 |
answered | Best database choice for a highly scalable web ticketing system? |
|
Jun 28 |
comment |
Optimizing query using view on EAV structure Yeah: your example fits my description precisely: it uses the index because of the condition on a = 1. If you remove the latter, the index won't be used. The more coercive to the left-most columns, the better the result. Now, in your case, the key is to have an index that the planner can use. You're looking for, say, a weight; so an index on (name) fits; one on (item, name) won't. As for partial indexes, think of them as having an extra column (the expression) tossed in the left-most position. B-tree indexes are traversed left to right. |
|
Jun 27 |
comment |
Optimizing query using view on EAV structure In the case of where width < 100, you've basically two options. One is the above-mentioned (name, value) (or even (name, value, item) for use in joins were Postgres aware of index-only scans (which it isn't)). The other is an index on (value) where (name = 'width') (or (value, item) where ..., had Postgres been index-only scan aware), which is a partial index (aka smaller...). |
|
Jun 27 |
comment |
Optimizing query using view on EAV structure Following up now that I'm no longer on an iDevice. It's not the same. The difference between (item, name) and (name, item) breaks down to what you look at first. In the first case, you do for each item list names and filter; in the second you do for each name list items and filter. In your query, you definitely want to do the latter rather than the former. In fact, you even want to use an index on (name, value) so that: for each name where name = cond1; for each value where cond2; yield item (for your join clause). |
|
Jun 27 |
awarded | Commentator |
|
Jun 27 |
comment |
Optimizing query using view on EAV structure Your example plan, for instance, could use an index on (value) where (name = 'width'). I'll bet the house that it'll prefer an index scan rather than a seq scan if you add it. If you add an index on (name, value) it'll likely do so as well. |
|
Jun 27 |
comment |
Optimizing query using view on EAV structure If you've an index on (item, name) postgres will use the index if you've a constraint on item; the other way around, postgres will use them if you've a constraint on name. in your case, you want that to happen: only items with name equal to weight, rather than all ids filtered by name... |
|
Jun 27 |
comment |
Optimizing query using view on EAV structure Yep, but for optimal planning, your query needs one with name to the left. Using your primary key index, Postgres would need to look up items one by one, and test for the name. What you want it to do, is to look up a single name, and to return every applicable item in one pass. |
|
Jun 27 |
answered | Optimizing query using view on EAV structure |
