On 27/06/14 14:01, Scott Marlowe wrote:
On Thu, Jun 26, 2014 at 5:49 PM, Mark Kirkwood
wrote:
I have a nice toy to play with: Dell R920 with 60 cores and 1TB ram [1].
The context is the current machine in use by the customer is a 32 core one,
and due to growth we are looking at something large
On Thu, Jun 26, 2014 at 5:49 PM, Mark Kirkwood
wrote:
> I have a nice toy to play with: Dell R920 with 60 cores and 1TB ram [1].
>
> The context is the current machine in use by the customer is a 32 core one,
> and due to growth we are looking at something larger (hence 60 cores).
>
> Some initial
I have a nice toy to play with: Dell R920 with 60 cores and 1TB ram [1].
The context is the current machine in use by the customer is a 32 core
one, and due to growth we are looking at something larger (hence 60 cores).
Some initial tests show similar pgbench read only performance to what
Rob
>I'm curious to see if Aaron can test his structure on 9.3 with the
>original data and WHERE clause and see if the planner still goes for
>the
>terrible plan. If it does, that would seem like an obvious planner
>tweak
>to me.
I will try to spin up a test 9.3 db and run the same queries to se
>The PK of the master table and the PK of the detail table cannot be
>the same thing, or they would not have a master-detail relationship.
>One side has to be an FK, not a PK.
>
Of course this is correct. I was trying to make the point that there should be
unique indices (of whatever flavor PG
On 06/26/2014 03:14 PM, Jeff Janes wrote:
If that is 50 PKs from the master table, it would be about 1000 on the
detail table.
You're right. But here's the funny part: we solved this after we noticed
his where clause was directed at the *detail* table instead of the
master table. This was co
On Wed, Jun 25, 2014 at 2:40 PM, Aaron Weber wrote:
> I will gather the other data tonight. Thank you.
>
> In the meantime, I guess I wasn't clear about some other particulars
> The query's where clause is only an "IN", with a list of id's (those I
> mentioned are the PK), and the join is explicit
On 6/26/2014 12:23 PM, Shaun Thomas wrote:
On 06/26/2014 11:19 AM, Claudio Freire wrote:
Try changing node_id in (...) into node.id in (...)
That looks much better to my untrained eye! (Am I right?)
Nested Loop (cost=218.29..21305.47 rows=53480 width=187) (actual
time=42.347..
43.617
On Thu, Jun 26, 2014 at 10:37 AM, Shaun Thomas
wrote:
> On 06/26/2014 09:22 AM, AJ Weber wrote:
>
> I sent the details as identified by pgAdmin III.
>>
>
> Interesting. Either there is a bug in pgAdmin, or you're connecting to a
> different database that is missing the primary key. What is the E
On 06/26/2014 11:19 AM, Claudio Freire wrote:
Try changing node_id in (...) into node.id in (...)
Wow. How did we not see that earlier? That's probably the issue. If you
look at the estimates of his query:
Bitmap Heap Scan on alf_node_properties prop (cost=1253.19..189491.87
rows=52790 wi
On Thu, Jun 26, 2014 at 12:48 PM, AJ Weber wrote:
> FWIW: I tested removing the quotes around each value, and it did not change
> the plan (I am a little surprised too, but I guess PG is "smarter than
> that").
>
> Thanks for the idea.
Ok, second round.
Try changing node_id in (...) into node.i
FWIW: I tested removing the quotes around each value, and it did not
change the plan (I am a little surprised too, but I guess PG is "smarter
than that").
Thanks for the idea.
On 6/26/2014 11:38 AM, AJ Weber wrote:
I noticed this too. I am trying to find where the actual SQL is
generated, an
On Thu, Jun 26, 2014 at 12:38 PM, AJ Weber wrote:
> I noticed this too. I am trying to find where the actual SQL is generated,
> and I am seeing if this is an artifact of Hibernate.
>
> Will test the same query without the quotes as you recommend. (But I don't
> know where to fix that, if it is
I noticed this too. I am trying to find where the actual SQL is
generated, and I am seeing if this is an artifact of Hibernate.
Will test the same query without the quotes as you recommend. (But I
don't know where to fix that, if it is the actual issue, unfortunately.)
On 6/26/2014 11:35 AM
On Thu, Jun 26, 2014 at 10:26 AM, AJ Weber wrote:
> OK, the sample query is attached (hopefully attachments are allowed) as
> "query.sql".
> The "master table" definition is attached as "table1.sql".
> The "detail table" definition is attached as "table2.sql".
> The EXPLAIN (ANALYZE, BUFFERS) outp
From psql (same session as previous \d output) --
Hash Join (cost=328182.35..548154.83 rows=52790 width=187) (actual
time=4157.886..4965.466 rows=1071 loops=1)
Hash Cond: (prop.node_id = node.id)
Buffers: shared hit=146711 read=23498, temp read=23676 written=23646
-> Bitmap Heap Sca
On 06/26/2014 09:22 AM, AJ Weber wrote:
I sent the details as identified by pgAdmin III.
Interesting. Either there is a bug in pgAdmin, or you're connecting to a
different database that is missing the primary key. What is the EXPLAIN
ANALYZE output if you execute the query you sent on a psql
I sent the details as identified by pgAdmin III.
psql output shows this:
\d alf_node
Table "public.alf_node"
Column | Type | Modifiers
++---
id | bigint | not null
version
On Thu, Jun 26, 2014 at 11:07 AM, AJ Weber wrote:
> I will try this, but can you clarify the syntax? I only know the VALUES
> clause from insert statements, and it would be one set of parens like
> VALUES('175769', '175771', ... )
>
>
That is for multiple columns, mine is for multiple rows (and
I will try this, but can you clarify the syntax? I only know the VALUES
clause from insert statements, and it would be one set of parens like
VALUES('175769', '175771', ... )
You seem to indicate a VALUES clause that has strange parenthesis
corresponding to it.
Thank you for the feedback an
On 06/26/2014 08:26 AM, AJ Weber wrote:
The "master table" definition is attached as "table1.sql".
The "detail table" definition is attached as "table2.sql".
I'm not sure what you think a primary key is, but neither of these
tables have one. Primary keys are declared one of two ways:
CREATE
On Thu, Jun 26, 2014 at 10:26 AM, AJ Weber wrote:
> OK, the sample query is attached (hopefully attachments are allowed) as
> "query.sql".
> The "master table" definition is attached as "table1.sql".
> The "detail table" definition is attached as "table2.sql".
> The EXPLAIN (ANALYZE, BUFFERS) out
OK, the sample query is attached (hopefully attachments are allowed) as
"query.sql".
The "master table" definition is attached as "table1.sql".
The "detail table" definition is attached as "table2.sql".
The EXPLAIN (ANALYZE, BUFFERS) output is here:
http://explain.depesz.com/s/vd5
Let me know
23 matches
Mail list logo
