The Heap™ – Consultancy ©®

John K. N.

6:25 AM

@mustaccio But you might want to try White Mountain Brewery's beer.

Morning

Vérace

7:47 AM

Dia dhaoibh go léir a chairde!

Andriy M

8:08 AM

Morning

Vérace

@ypercubeᵀᴹ "COUNT(dt.c_date)" - yes, of course! I went down a rabbit-hole when I saw the other answer "SUM(t1.data IS NOT NULL)," and basically shoe-horned the SUM() function to do what I wanted - thanks for pointing out my schoolboy error! I also went down another hole with regular expressions (coming to a screen near you!)!

@ypercubeᵀᴹ And that ORDER BY 1 hurts my eyes ;) - Sorry ducky! :-)

@ypercubeᵀᴹ "Good answer nonetheless." - thanks - I put a bit of time into it, but I learned a good bit, so it was worth it! "Don't get offended by my late night comments ..." - it takes a lot to offend me! :-)

@mustaccio Re. Guinness outside of Ireland - I used to drink Guinness regularly, and the best I had was in Paris - I'm pretty sure it came from Dublin - but because it was considered a "premium export" product, it was considerably stronger than its Dublin counterpart - and much more expensive!

John K. N.

Funny: The query to retrieve regressed queries from the Query Store has regressed.

brentozar.com/pastetheplan/?id=SJeNA_T0D

McNets

Morning

John K. N.

8:26 AM

Andriy M

@Vérace The query that goes by the Query_ID of 3 in your profile chart has more aggregations in it than the other queries. I mean it's performing the SUM and the COUNT twice. Maybe internally they are run once each since they are just instances of identical aggregations, but maybe not. I don't know how much of a factor it can be if not, but since we are talking less than milliseconds, it might matter some.

Vérace

@AndriyM - you mean the query where the schema doesn't change? Yes, I know it's longer - I kept it in because the OP may not be able to make any mods to the schema.

Andriy M

I mean the only one that calculates the percentage

The other three are not doing that, though it didn't help the slowest one, of course

Vérace

@AndriyM - God alone knows what MySQL does! The one that calculates the percentage is also the only one where there's a SELF-JOIN - that join is performed already in the VIEW or the TABLE schema adjustments, so no double table-scan - at least AIUI?

Andriy M

But the one over the view is effectively also done over the self-join, no? I mean if we resolve the view. But as you say, we don't know what MySQL does, so maybe there's a difference.

I'm looking at this mostly from a SQL Server perspective, where it would be little to no difference in querying over a join vs querying over a view that's defined as the exact same join.

Vérace

8:38 AM

I mentioned that I went down a rabbit hole about regular expressions - here is PostgreSQL's and MySQL's response to the same REGEXP_REPLACE query - I was trying to find out which was the most efficient for converting a string in DD-MM-YYYY format to ISO YYYY-MM-DD format - I wanted a "universal" solution - but I ended up going back to SUBSTRING and the || operator - you have to use a SQL sql_mode = ANSI; hack to get MySQL to do the right thing - so I could CAST the string as a date.

Here is a side-by-side - PostgreSQL does (what I think) is the right thing - MySQL apparently does nothing AND doesn't even throw an error - report a bug or shrug the shoulders?...

Andriy M

Was that the right fiddle? I'm not sure what I'm supposed to be looking at there.

Vérace

@AndriyM - the fiddle with the regexes has nothing to do with the solution - it's just something I noticed while working towards my solution - one of many rabbit-holes! The final final solution here now has 4 queries all profiled and explained (thanks to @ypercubeᵀᴹ).

ypercubeᵀᴹ

9:08 AM

I try to avoid regexes but I do know that they differ from dbms to dbms

so it's not weird that you get different results or errors between Postgres and mysql

@Vérace what is the fail exactly? I don't see anything

Perhaps dbfiddle shows only a single result when they are identical from the 2 dbms?

Andriy M

After commenting out the percentage calculation, query 3 ends up the fastest of the four: dbfiddle.uk/…

ypercubeᵀᴹ

Never mind my previous comment Verace, I see now. The date strings are not converted

Andriy M

I'm surprised, especially since I myself would much prefer COUNT(column) to the ugly SUM(CASE ...)

(and would expect the former to be faster than the latter)

@ypercubeᵀᴹ That was helpful, I can now see the issue myself.

Ha, query 3 in my fiddle had a syntax error, how embarrassing

Fixed: dbfiddle.uk/…

It now runs consistently with the third fastest result, but queries 1 and 2 sometimes trade places. Look like this profiling thingy is best done in a different environment than an online publicly available resource

Peter Vandivier

9:28 AM

feel like i've asked this before but... @MaxVernon, @jcolebrand - when "no-longer-needed" flagging a comment that's part of a discussion, it's preferable to just flag one, right?

given that a mod is gonna go to the comment thread to clean it up anyway, i assume you just use your judgement from there

rather than flagging all the individual comments that could apply, i mean

Andriy M

FWIW, when I want to flag an entire discussion as no longer needed and don't feel like flagging every comment individually, I flag the last one with a custom message. It's worked with the intended result on DBA.SE so far. I've recently done that on SO and they removed only the comment I flagged. I then flagged the other comments individually as "no longer needed".

Different communities, different flagging cultures

Different attitude to comments too, perhaps

Peter Vandivier

Different everything, really. Each network is really self-contained IMO. I don't feel like there's not a whole lot that's portable between SE communities in terms of social expectation.

> Theoretically, there are can be any number of clustering indexes

rather than saying "lolwat" and downvoting... anyone know what he's talking about?

pretty sure it's just straight up wrong in the context of that thread but I'm wondering what I'm missing that he's interpreting from it as a storage admin?

Andriy M

9:43 AM

For starters, why is a clustering index called as such? Genuine question from a non-native English speaker

So far I've simply accepted the term as is, but when answering questions like yours it would be great to understand the terminology better

Peter Vandivier

no idea, really. if i had to make up a post-facto justification for it right now i'd say that it's cause it "keeps the data together", like a cluster of hosts keeps them together on the network or something 🤷‍♂️

percona.com/blog/2009/05/27/…

i wonder if it's just multiple copies of the table 🤔

Andriy M

@PeterVandivier That's actually what I'm inclined to think the answerer meant

John K. N.

@AndriyM From IBM: A clustering index determines how rows are physically ordered (clustered) in a table space. Clustering indexes provide significant performance advantages in some operations, particularly those that involve many records.

Peter Vandivier

dictionary.cambridge.org/dictionary/english/cluster

> a group of similar things that are close together

John K. N.

So as per Zardosht Kasheff answer, there are products that can have mulitple clusted indexs, which is basically duplicating the data, because a custerd index is "the way/order" that data is stored in a table.

Andriy M

9:47 AM

@JohnK.N. I mean, I can probably find other similar definitions. I'm trying to understand the choice of the word to describe it, though. What is "clustering" about it? It's also sometimes called "clustered", which grammatically speaking shouldn't be the same thing, but in this case the two words are used synonymously

A non-clustered (non-clustering?) index also provides a way to order rows, albeit indirectly

John K. N.

clustered (adj.) index = cluster (noun) index (IMO)

@AndriyM via B-Tree.

But if you already have the data via the clustered index, then there is no need for additional lookups.

Andriy M

If you create multiple indexes on all the columns but in different combinations thereof, that would effectively give you multiple clustered indexes, right?

John K. N.

@AndriyM not in SQL Server.

Andriy M

It's called "clustered" to mean precisely "containing all the data". Would that be a correct understanding of the term?

John K. N.

SQL Server tables only have one clustered index.

Andriy M

9:53 AM

@JohnK.N. Formally. But I'm talking effectively

John K. N.

I guess it is because the data is "clustered" in the order it is stored on disk.

Good answer here:

324

A: What do Clustered and Non clustered index actually mean?

In SQL Server, row-oriented storage both clustered and nonclustered indexes are organized as B trees. (Image Source) The key difference between clustered indexes and non clustered indexes is that the leaf level of the clustered index is the table. This has two implications. The rows on the clus...

Andriy M

But none other than Martin Smith

John K. N.

From Martin Smith.

Andriy M

thanks

John K. N.

@AndriyM no effectively. In the end the other indexes reference either the clustered index (pages) or (a) heap (pages).

Pointers vs. data.

ypercubeᵀᴹ

10:02 AM

@JohnK.N. we know, yes.

The answer that Peter linked to is talking about theoretical possibilities and other implementations.

Andriy M

But again, as the answer linked by Peter argues, that may just be the implementation choice by SQL Server. Maybe a clustered index doesn't have to be the table, it could be one of multiple copies of the table.

@ypercubeᵀᴹ Exactly

ypercubeᵀᴹ

They give an example (engine TokuDB in mysql) that allows for multiple clustered indexes.

Andriy M

That fact that it's space-consuming is certainly a factor in many scenarios, but it may not be in some others

Peter Vandivier

coming from SQL Server, the way I usually explain it to people is that the clustered index (assuming it exists) is the table

so saying "multiple clustered indexes", sounds to me like "multiple tables" and i assume there's some bizarro (to me) trigger-type storage mechanism that enforces concurrent updates between them

ypercubeᵀᴹ

@JohnK.N. other indexes are not just pointers though. They have some data, i.e. the data in the index (key) columns. Plus the included columns.

@PeterVandivier in essense they are. Multiple copies of the same table, stored in different order.

Andriy M

10:13 AM

Martin actually falls short of actually explaining the difference between the clustered index and a non-clustered index with all the columns in SQL Server. He gives this example:

Peter Vandivier

@ypercubeᵀᴹ yea, typing it out made me think that "gee, i guess it's really just the same as NC indexes"

Andriy M

CREATE TABLE T
(
A INT,
B INT,
C INT,
D INT
)

CREATE UNIQUE CLUSTERED INDEX ci ON T(A, B)
CREATE UNIQUE NONCLUSTERED INDEX nci ON T(A, B) INCLUDE (C, D)

and follows it by:

> The two indexes above will be nearly identical. With the upper-level index pages containing values for the key columns A, B and the leaf level pages containing A, B, C, D

I guess it's the "nearly" part that would be interesting in the context of this discussion

ypercubeᵀᴹ

The mechanism (I assume) is no more different than updating a non-clustered index in SQL Server that has all the rest columns as INCLUDEd (like Andriy just shows ^^).

@AndriyM No idea about the actual details that differ between the two. Possibly different overheads on rows and pages metadata.

One actual difference is that if we add a column to the table, it will be added only in the clustered index.

Andriy M

Perhaps NC indexes always have a reference to the actual table (clustered index or heap) regardless of whether you've included all the columns and (maybe?) regardless of whether it's also a unique index with all the columns. A clustered index, of course, doesn't need to have such references.

In an environment with multiple clustered indexes, it would probably make sense to have the concept of a primary copy, though, at least if the environment supports non-clustered indexes as well, because those would need to reference something in case the index merely helps to quickly point to a row but you need other columns from it that aren't covered by the (non-clustered) index.

Which makes me think that it wouldn't be very beneficial – and instead could be somewhat confusing – to support both non-clustered indexes and multiple clustered indexes. It's either a single clustered index and multiple supporting non-clustered ones, or just multiple indexes all of which are clustered

John K. N.

10:35 AM

@AndriyM +1

The multi-clustered-index products will probably copy the data multiple times. Hence the disk space overhead mentioned. However, multi-clustered-index solutions have a speed advantage, because no additional lookup required. The clustered index is the data.

ypercubeᵀᴹ

@AndriyM I don't think there is any reference needed. All the columns of the table are included in the index and that's all you need to access the table/main-clustered-index if needed.

But I agree, a main/primary-copy clustered index makes sense.

That's what TokuDB does, as far as I know.

So you can have non-clustered indexes as well, that work exactly as in InnoDB / SQL Server.

Andriy M

@ypercubeᵀᴹ But as you said, when a new column is added, the non-clustered index won't have it, only the clustered one will, so that's probably why there reference still needs to be there.

ypercubeᵀᴹ

why do you need a reference?

When a new column is added to the table, it is added in all clustered indexes.

You only need a reference when the table is a heap.

Andriy M

@ypercubeᵀᴹ We were talking about differences between clustered vs non-clustered (but with all the columns) there.

ypercubeᵀᴹ

ok, yeah. got confused a bit with the parallel discussion.

I still don't see why you need a reference though when the table has all the columns included

it still has included all the PK columns, when the clustered index is based on the PK.

Vérace

10:47 AM

@ypercubeᵀᴹ The fiddle's failure is that MySQL lower result doesn't change 07-07-2027 into 2027-07-07 as the PostgreSQL upper result does - so that the string can then be CASTto a DATE - which is what I wanted to do - I ended up using a SUBSTRING so that the SQL would be multi-server compatible!

ypercubeᵀᴹ

(or the clustered index key columns, when not)

Andriy M

@ypercubeᵀᴹ That's the detail that I don't really know much about, hence my saying "perhaps" at the beginning. If SQL Server recognises that the key of a non-clustered index matches the primary key exactly, then maybe there's no additional reference needed, but I just don't know

ypercubeᵀᴹ

matches or not, I don't think it does.

Say the index is T(A, D) INCLUDE (B, C) why would we need a reference?

Andriy M

When you add a column E, it's added to the clustered index only. And if a query happens to use the non-clustered index to find a row but suddenly realises it needs to pull data from E, which isn't in the non-clustered index, what then?

That's why some kind of referencing may be needed

ypercubeᵀᴹ

but the NCI still has the columns of the PK/CI index, right? So, a plan that gets data from a NCI index first, it can then traverse the CI index and find any row it needs and the wanted extra column data

Vérace

10:52 AM

@AndriyM - thanks for your interest - on my first 3 runs of your fiddle (corrected the comma), the 3rd query was the 2nd slowest - you have to look at multiple runs - you don't know what's happening on the dbfiddle.uk server at any given time, so a single run snapshot may not be representative?

Andriy M

@ypercubeᵀᴹ Right, which is where my knowledge gives way. If the keys match, then of course that it makes sense that the key can be used as a way of referencing, particularly if the key is unique.

ypercubeᵀᴹ

I am confused why you think we need the keys to match

Example

CREATE UNIQUE CLUSTERED INDEX ci ON T(A, B)
CREATE UNIQUE NONCLUSTERED INDEX nci ON T(C, D) INCLUDE (A, B)

Then we add a column E

so it becomes like

CREATE UNIQUE CLUSTERED INDEX ci ON T(A, B) -- but this has C, D, E
CREATE UNIQUE NONCLUSTERED INDEX nci ON T(C, D) INCLUDE (A, B)

Andriy M

@Vérace A single run should certainly not be relied on when it's a publicly available resource that may be running you don't know how many other user queries.

ypercubeᵀᴹ

say now we add another column F and we also change the PK/CI to be (A, E)

CREATE UNIQUE CLUSTERED INDEX ci ON T(A, E) -- but this has B, C, D, F as well

I think (if I am not wrong) that the nci will automatically change as well

Andriy M

Change to what?

ypercubeᵀᴹ

11:00 AM

CREATE UNIQUE NONCLUSTERED INDEX nci ON T(C, D) INCLUDE (A, B) -- and secretly INCLUDE(E)

because it needs to have the (A, E) data to be able to reference rows in the CI

Andriy M

Well then that's exactly the kind of referencing overhead I'm talking about.

The kind that a non-clustered index needs and a clustered index doesn't. Note that you may still want to pull from column F, which the NC index doesn't have at that point

Vérace

@AndriyM - strangely the SUM() query with the (already constructed) table (2nd query in the fiddle) is fastest of all!... and not the apparently simpler COUNT() one (table also) ... go figure... I think a dive into PG source would be in order to figure that out - above my pay grade... :-)

> 6 runs - all runs showed this effect!

Andriy M

@Vérace A dive into PG source would be needed to figure out why SUM(CASE...) is faster than COUNT(column) in MySQL?

Vérace

Oh, Jesus - I'm getting confused between regexes, SUMs, COUNTs, life, the universe and everything... A dive into MySQL code would be required...

Andriy M

Well, the answer is 42 anyway

ypercubeᵀᴹ

11:06 AM

@Vérace sorry, we are profiling queries on a 10-rows table?

or did you test on a bigger table?

Not that it's entirely useless. Try this: dbfiddle.uk/…

now the COUNT is faster ;)

Andriy M

You used the same data source! That's cheating!

Ah, no, it's the same in the original. You changed the order of queries

That's cheating!

Vérace

@ypercubeᵀᴹ - ah, you changed the order of the queries - I thought I'd mentioned that in the answer somewhere - caches &c. will obviously come into play! If you've stored the table in memory in the cache, queries lower down will speed up.

I could use PG's generate_series to generate millions of records (I did search for a CLEAR CACHE command - tried something that didn't work (forgot what). Then use pg_dump to generate generic SQL and load it into MySQL...

Is there any way of telling MySQL on dbfiddle to clear its cache - or PostgreSQL for that matter?

ypercubeᵀᴹ

I don't know

Andriy M

You can always try repeating the same query during profiling though

ypercubeᵀᴹ

You could probably try a bigger table (not necessarily in the millions, don't kill dbfiddle ;) say 5-10k rows and repeat the query. If you can ignore the 1st run even better.

So the opposite of clear cache

Andriy M

11:18 AM

It's not really difficult to ignore the 1st run, don't know why but I'm sure @Vérace can

Vérace

@ypercubeᵀᴹ - of course, as you suggest, profiling a query with a tiny number of records is not a definitive performance analysis... I might look at that this evening... I can run it on my local machine - and clear down the caches... Another problem is that we don't know the OP's data distribution - I would imagine that the vast majority of garments are collected? PG's partial indexes would be good for this - but not with MySQL...

And why on earth would one be interested in the colour of non-collected garments? Do Goths not pick up their stuff?

Andriy M

@Vérace Exactly. We don't know if they do, so that might be why one would interested to know that stuff

Max Vernon

12:04 PM

This appears to be out-of-scope to me, but maybe I'm wrong? I know nothing about Alembic.

John K. N.

@MaxVernon Agree as per my comment:

I’m voting to close this question because it is related to Alembic which is explained as "Alembic is a lightweight database migration tool for usage with the SQLAlchemy Database Toolkit for Python." I'd suggest this question be asked on Stack Overflow where there is an alembic tag and one for sqlalchemy. Not off-topic but better suited on SO. — John K. N. Dec 24 '20 at 7:52

However the question was asked on SO apparently and then on DBA.SE.

See comment section on question.l

It was in the reopen queue, but I skipped it.

Max Vernon

1:09 PM

yah, I looked at everything already - mostly I was wondering if my own biases are preventing me from seeing something that others can see. Like for instance, the OP states they are trying to use this on their production database, but all I see is version control and hence I think client side. I mean maybe if they didn't keep talking about their developers, I wouldn't have a problem with it. It's not like as DBAs we're inherently anti-source control.

@PeterVandivier my personal preference would be to have you flag everything that you think can go as "no longer needed".

Andriy M

1:35 PM

I'm surprised that this one was closed as unclear rather than off-topic:

-1

Q: Running a stored procedure in a loop crash before ending

For reasons of performance, I need to run in parallel multiple stored procedures (actually, the same SP, with different parameters) and combine the results in code. It is not possible to read all the data right away. It works perfectly with a small number of concurrents, but with 19, I receive a ...

The issue might end up being in the database, but the investigation should clearly start on the client side

Max Vernon

1:54 PM

agreed

mustaccio

2:04 PM

The term "clustering index" was already in use by the IBM researchers working on System R as early as 1981 (before Sybase and MS SQL Server, that is): "An index is considered to have the clustering property if the key order of the index corresponds closely to the ordering of records in physical storage". Example: dsf.berkeley.edu/cs262/SystemR-annotated.pdf

In the IBM implementation row data are not stored in the index pages -- a table is always a "heap". If there is an index declared as clustering, the engine will try to place the row near other rows with the same/similar key upon insert or key update. There is no guarantee though.

I guess that's why it's "clustering" -- it tries to achieve clustering of data.

Max Vernon

interesting 😊

bbaird

3:08 PM

Good morning

bbaird

3:21 PM

@mustaccio IIRC Oracle and IBM use the terms "index-organized" since the data is still in a heap, but there's still a page/row pointer used in the B-tree (as opposed to having the leaf level be the table itself).

mustaccio

Oracle does use the term "index-organized table", but Db2 has always used "clustering index" (sometimes conflated with "clustered", but "clustering" is the primary term).

John K. N.

@AndriyM yeah, mixed feelings.

bbaird

@mustaccio I've only done queries against DB2, never had to do any of the nuts and bolts stuff

John K. N.

Why not just set the sql.Cmd.CommandTimeout = 600? The exception you are observing is the expected behaviour in my opinion. — John K. N. 3 hours ago

mustaccio

@bbaird I won't hold it against you.

There's probably more nuts than bolts in Db2

2

bbaird

3:34 PM

I think SQL Server development would benefit on a whole if people understood that the physical implementation of "nonclustered indexes" are no different that tables clustered by the indexed column(s) with the original PK and any included columns as the data.

Cade Roux

4:37 PM

The term Clustered Index is probably the worst thing when dealing with explaining things to people like execution plans and index design and you have to keep using the term, and always clarifying with statements like (which isn't an index, it's just the table, meaning not a heap, and on and on). Sometimes when I see clustered index scan in the plan and I'm showing it to something, I just say "table scan", because fuck it.

And yeah, NCI is really just another clustered index. Because all indexes are clustered!

3

ypercubeᵀᴹ

4:49 PM

@CadeRoux Amen to that!

ypercubeᵀᴹ

5:07 PM

and by that we mean B-tree indexes of course ;)

Max Vernon

7:12 PM

@ypercubeᵀᴹ all the indexes that aren't not clustered

Andriy M