Notices by Toksyuryel (toksyuryel) tagged sql

Toksyuryel Coder Ponies! , deadname , Bit Shift

Before I go to bed, I wanted to share this because it makes me happy. http://dpaste.com/749403/ @widget @bitshift !coderpony #SQL

Toksyuryel Coder Ponies! , deadname , Bit Shift

Pre-caching the path instead of generating it dynamically seems to only improve cached performance (though the improvement there is dramatic). In some cases the uncached performance was actually *worse* than generating the path in a WITH RECURSIVE block. http://pgsql.privatepaste.com/60741d539f @bitshift @widget !coderpony #SQL

Toksyuryel Coder Ponies! , deadname , Bit Shift

So, first test: building the paths in a WITH RECURSIVE block under normal settings. Test shows that this is, indeed, unacceptably slow but cached performance is phenomenal until the thread gets monstrously huge; as real world threads will never get as big as even the smallest thread in this sample, and certainly won't be so spread out, I consider the cached performance to be acceptable but I'd like something better for uncached performance. Now let's test it with the pre-computed paths. http://pgsql.privatepaste.com/205d6138f2 @bitshift @widget !coderpony #SQL

Toksyuryel Coder Ponies!

Hm, that was done faster than I thought. I estimated the total size would get to be over 5GB but it only got to 4.1GB before it finished. Here's an hour-by-hour breakdown of how long it took to populate the database with over ten million rows: http://dpaste.com/747878/ !coderpony #SQL

Toksyuryel

At one million rows, the performance increase of sorting on a pre-computed path is negligible compared to generating the path in a WITH RECURSIVE block. I'll generate another ten million rows overnight and see how that performs tomorrow. #SQL

Toksyuryel Coder Ponies! , deadname , Bit Shift

I can't understand what this error is about. All of my parenthesis seem to line up properly (and it successfully creates the first two tables before the one with the error) and I don't see any misplaced or missing semicolons. Any help? http://dpaste.com/747347/ @bitshift @widget !coderpony #SQL

Toksyuryel

Also I do believe this is the first valid use of an array I've found. #SQL

Toksyuryel

Alright I figured out what I need to do. If we store the path to each post directly in the database, there is no need for a recursive query. Sorting is fast and fetching posts by thread is also fast, so as long as these are the only things we're doing the result will be fast. Unfortunately testing this against ten million posts will have to wait until tomorrow as implementing this requires updating the schema. I believe that there is nothing further I can do with this database so it's time to drop it and get that new schema in there. #SQL

Toksyuryel Coder Ponies! , deadname , Bit Shift

Here's a comparison of uncached performance vs cached performance http://dpaste.com/747276/ @bitshift @widget !coderpony #SQL

Toksyuryel

Performance is pretty terrible with ten million rows. I'll have to work out a way to deal with it. #SQL

Toksyuryel

I wonder how long ago this would have been done if I could use COPY. I started populating four and a half hours ago and it's not even two-thirds done yet. #SQL

Toksyuryel

Just to see how well this really scales, let's try it with ten million pots. Predictions? #SQL

Toksyuryel Coder Ponies! , deadname , Bit Shift

I'm pretty happy about this. http://dpaste.com/746779/ @widget @bitshift !coderpony #SQL

Toksyuryel

Good news: I've identified what sort of query I need in order to output conversations sorted as threads. Bad news: it's WITH RECURSIVE. #SQL

Toksyuryel

Also the fact that CHECK constraints can contain functions in general really. #SQL

Toksyuryel

I have discovered that CHECK constraints can contain subqueries if you encapsulate them inside of functions. This opens up so many possibilities. #SQL

Toksyuryel

Learning quite a few things about what I've been doing wrong. It's great to be able to finally test this stuff. #SQL

Toksyuryel

I wonder if it's preferred to chain table joins or use subqueries... it seems like both ultimately accomplish the same thing, just in different ways. Which performs better? Something I'll have to stop being lazy about rebooting to check. (technically I could check now but I don't really want to while still on the LiveCD) #SQL

Toksyuryel

So many joins @.@ #SQL

Toksyuryel Coder Ponies!

A question arises: if a column references its own table as a foreign key with ON DELETE RESTRICT and just so happens to refer to its own row, is that row simply completely impossible to delete or will it be deleted normally? !coderpony #SQL

Toksyuryel Coder Ponies! , Bit Shift

Today's lesson: arrays are evil. http://dpaste.com/745811/ @bitshift !coderpony #SQL

Toksyuryel

Hm, I think my problem is not enough tables. Let's refactor this whole thing and make like a bajilion tables. #SQL

Toksyuryel Coder Ponies!

Did I do this right? ` replyto bigint CHECK (replyto IS NOT postid AND replyto IS CASE WHEN conversation IS NOT postid THEN NOT NULL),` !coderpony #SQL