On Wed, Jul 15, 2015 at 12:32 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
>
> You may be able to put Table Inheritance to good use here...
>
> I do not know (but doubt) if HOT optimization works when going from NULL
> to non-NULL since the former is stored in a bitmap while the late
On Wed, Jul 15, 2015 at 11:15 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
>
> Yes, you are trying to choose between a bunch of one-to-one (optional)
> relationships versus adding additional columns to a table all of which can
> be null.
>
> I'd argue that neither option is "normal
On Wed, Jul 15, 2015 at 10:33 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Wednesday, July 15, 2015, Robert DiFalco
> wrote:
>
>> First off I apologize if this is question has been beaten to death. I've
>> looked around for a simple answer and c
First off I apologize if this is question has been beaten to death. I've
looked around for a simple answer and could not find one.
Given a database that will not have it's PKEY or indices modified, is it
generally faster to INSERT or UPDATE data. And if there is a performance
difference is it subs
This may fall into the category of over-optimization but I've become
curious.
I have a user table with about 14 columns that are all 1:1 data - so they
can't be normalized.
When I insert a row all columns need to be set. But when I update, I
sometimes only update 1-2 columns at a time. Does the n
Jeff, I'm not seeing that limitation.
On Thu, Dec 18, 2014 at 10:33 AM, Jeff Janes wrote:
>
> On Thu, Dec 18, 2014 at 10:00 AM, Giuseppe Broccolo <
> giuseppe.brocc...@2ndquadrant.it> wrote:
>>
>> I'm not sure about the '%' operator, but I'm sure that the GIST index
>> will never be used in the
>
I'm pretty sure '%John%' uses the index.
explain analyze verbose SELECT name FROM wai_users WHERE lower(name) LIKE
'%john%';
QUERY PLAN
--
I know! I was surprised that % 'John' or % 'JOHN' or even % 'jOhn' all
returned the same result.
Besides readability would there be any technical differences between a GIST
index that is lower or not and using LIKE vs. %?
Thanks!
On Thu, Dec 18, 2014 at 9:18 A
So, for my use case I simply need to search for a case insensitive
substring. It need not be super exact. It seems like there are two ways I
can do this:
CREATE INDEX idx_users_name ON users USING GIST(lower(name) gist_trgm_ops);
SELECT * FROM users WHERE lower(name) LIKE '%john%';
Or I can do it
ch and the completely normalized set of tables approach. Thanks for
your input!
On Tue, Apr 15, 2014 at 8:12 AM, Chris Curvey wrote:
>
>
>
> On Tue, Apr 15, 2014 at 10:56 AM, Chris Curvey wrote:
>
>> On Mon, Apr 14, 2014 at 12:27 PM, Robert DiFalco <
>> robert.difa...
I have several related tables that represent a call state. Let's think of
these as phone calls to simplify things. Sometimes I need to determine the
last time a user was called, the last time a user answered a call, or the
last time a user completed a call.
The basic schema is something like this:
I have a question about replacing NULL columns with tables. Say I have a
table called Contacts that represents the Contacts (say from Facebook) of
my Users. Right now Contacts has several columns and in addition to these
it has an Owner (the user that owns this contact) and User. The User is
NULL i
So far that one was the worst in terms of cost and time. Here are all the
plans with buffers, more records, and results being returned. At this point
I have good enough performance with my UNION approach but I'm just trying
to learn now. WHY is the union approach the fastest? I would have expected
No I didn't, thank you. I missed the parens.
On Thu, Nov 21, 2013 at 2:58 PM, bricklen wrote:
> On Thu, Nov 21, 2013 at 2:04 PM, Robert DiFalco
> wrote:
>
>> H...I'm not sure why the buffers option didn't work for me, maybe the
>> heroku psql is out o
ns than the subselect approach?
On Thu, Nov 21, 2013 at 1:22 PM, Elliot wrote:
> On 2013-11-21 16:12, Robert DiFalco wrote:
>
> Sorry I couldn't get buffers to work but here is the explain analyze
> verbose:
>
> dft1fjfv106r48=> ex
I have found this:
SELECT c.*
FROM contacts c
WHERE c.id IN ( SELECT p.contact_id FROM phone p WHERE p.addr = ? )
OR c.id IN (SELECT e.contact_id FROM email e WHERE e.addr = ? );
To have a worse plan than:
SELECT * FROM contacts where id IN (
( SELECT c.id FROM contacts c
JOIN phone p ON c.id =
ntacts.details_hash, contacts.fname, contacts.lname,
contacts.fb_id
Index Cond: (contacts.id = c.id)
Total runtime: 0.332 ms
(31 rows)
On Thu, Nov 21, 2013 at 12:38 PM, desmodemone wrote:
> Could you please attache the plan with explain buffers verbose?
>
> thank you
>
>
and p.contact_id=
> c.id )
> OR exists (SELECT 1 FROM email e WHERE e.addr = ? and e.contact_id=c.id);
>
>
>
>
>
> 2013/11/21 Robert DiFalco
>
>> I have found this:
>>
>> SELECT c.*
>> FROM contacts c
>> WHERE c.id IN ( SELECT p.contact_
Currently I run two queries back-to-back to correlate users with contacts.
UPDATE contacts SET user_id = u.id
FROM my_users u
JOIN phone_numbers pn ON u.phone_significant = pn.significant
WHERE contacts.owner_id = 7 AND contacts.user_id IS NULL AND contacts.id =
pn.ref_contact_id;
UPDATE
ers u
(cost=0.00..0.12 rows=1 width=32) (never executed)
Output: u.phone_short
Index Cond: (u.phone_short = (e.significant)::text)
Heap Fetches: 0
Total runtime: 0.158 ms
(16 rows)
On Fri, Aug 9, 2013 at 8:44 AM, Kevin Grittner wrote:
> Robert DiFalco wrote:
>
Guys, let me know if I have not provided enough information on this post.
Thanks!
On Thu, Aug 8, 2013 at 11:06 AM, Robert DiFalco wrote:
> In my system a user can have external contacts. When I am bringing in
> external contacts I want to correlate any other existing users in the
> sy
In my system a user can have external contacts. When I am bringing in
external contacts I want to correlate any other existing users in the
system with those external contacts. A users external contacts may or may
not be users in my system. I have a user_id field in "contacts" that is
NULL if that
Thanks guys!
On Wed, Aug 7, 2013 at 11:35 AM, Igor Neyman wrote:
> > -Original Message-
> > From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-
> > performance-ow...@postgresql.org] On Behalf Of Claudio Freire
> > Sent: Wednesday, August 07, 2013 2:20 P
Let's say I have a table something like this:
create table call_activity (
id int8 not null,
called timestamp,
user_id int8 not null,
primary key (id)
foreign key (user_id) references my_users
)
I want to get the last call_activity record for a sing
wrote:
> Robert DiFalco wrote:
>
> > CREATE TABLE contacts
> > (
> > id BIGINT PRIMARY KEY NOT NULL, // generated
> >
> > blocked BOOL,
> > owner_id BIGINT NOT NULL,
> > user_id BIGINT,
> > FOREIGN KEY ( owner_id ) REFERENCES ap
ut those
are the only two reasonable variations I can think of.
The GROUP BY versus the DISTINCT on c.user_id makes no impact at all on the
plan. They are exactly the same.
On Sun, Jun 2, 2013 at 12:42 PM, Szymon Guz wrote:
> On 2 June 2013 21:39, Robert DiFalco wrote:
>
>> I have a
I have a table called contacts. It has a BIGINT owner_id which references a
record in the user table. It also has a BIGINT user_id which may be null.
Additionally it has a BOOLEAN blocked column to indicate if a contact is
blocked. The final detail is that multiple contacts for an owner may
referen
27 matches
Mail list logo
