The Heap™ – Consultancy ©®

1:03 AM

@ErikDarling sir, this is a Wendy's.

6 hours later…

7:30 AM

Morning

7:48 AM

Wordle 639 3/6

⬛⬛🟩⬛🟨
🟩🟩🟩⬛⬛
🟩🟩🟩🟩🟩

3 hours later…

John K. N.

10:24 AM

morning

Wordle 639 4/6

⬛⬛🟨⬛⬛
⬛🟩⬛⬛🟩
🟩🟩🟩⬛🟩
🟩🟩🟩🟩🟩

1 hour later…

11:35 AM

Can somebody back me up on this? The comment seems to be clearly in the wrong.

You answered the question correctly, but your additional advice is questionable. Very often, single-column indexes are the best choice, since they can be used for many queries. PostgreSQL can combine several index scans to speed up compound WHERE conditions. I'm not saying that single-column indexes are always the best choice. — Laurenz Albe 8 hours ago

If you are unioning two indexes, you ipso facto need a join between the results of the two indexes. In other words: lookup index on a for matches, lookup index b, and join the results to get a final result for a where on a and b. Single column indexes are rarely useful because it's quite rare to be querying just a single column in a table, with no other columns selected. Obviously there are certain cases when this might happen, but I would question it at first glance. — Charlieface 1 min ago

11:47 AM

In particular the docs seem to back me up. postgresql.org/docs/current/indexes-bitmap-scans.html

> because each additional index scan adds extra time, the planner will sometimes choose to use a simple index scan even though additional indexes are available that could have been used as well.

> A combination of the multicolumn index and a separate index on y would serve reasonably well.

Paul White

Wordle 640 3/6*

⬛⬛🟨⬛⬛
⬛🟨🟨🟨⬛
🟩🟩🟩🟩🟩

WordleArt

John K. N.

12:06 PM

@J.D. you missed the left curly bracket ;-)

lol. I always feel not worthy having my name shown as the last person to edit a Post, when it's a silly single character change like that.

But do know I read your entire answer and learned from it. 🙂

@Charlieface I'm not particularly versed in PostgreSQL, but for general indexing strategy theory my understanding is you're correct. With the choice of multipe single column indexes or a just as fitting composite index, it's been drilled into my head to go with the latter. I will say, I've had some odd interactions with Laurenz before, where generally he seems to know the PostgreSQL product well enough, but then once in a while he'll say something that doesn't make sense to me.

@J.D. I get that as well, might just be imposter syndrome...

John K. N.

@J.D. You're welcome.

Worddd

Or Wordle:

Wordle 639 4/6

⬛🟨🟨⬛⬛
⬛🟩🟩⬛🟩
⬛🟩🟩⬛🟩
🟩🟩🟩🟩🟩

Not my best efforts today.

Lamak

1:06 PM

Wordle 639 4/6

⬜🟩⬜⬜🟩
⬜🟩⬜⬜🟩
⬜🟩⬜🟩🟩
🟩🟩🟩🟩🟩

this game is too hard...I realize now that I don't know that many words

1 hour later…

2:18 PM

⬜⬜🟥⬜⬜
⬜🟥🟥🟥⬜
🟥🟥🟥🟥🟥
⬜🟥🟥🟥⬜
🟥🟥🟥🟥🟥

Has this chat just turned into a Wordle Consultancy?

i just draw stupid pictures with squares

that's a fire hydrant, i think

Question for the developers. Do you ever programmatically access git from C#? Do you use a library for that or some other way?

2:34 PM

nice twitter poll

2:47 PM

@ErikDarling It's missing some yellow squares

Hannah Vernon

2:59 PM

Wordle 639 6/6*

🟨⬛⬛⬛⬛
⬛⬛🟩⬛⬛
⬛⬛🟩⬛⬛
⬛⬛🟩⬛⬛
⬛🟨🟩⬛🟩
🟩🟩🟩🟩🟩

Phew is right

@Zikato I don't need to, there's one sitting in my chair already.

3:13 PM

@Charlieface JEAGL please?

@Zikato I haven't ever tried to do that.

3:34 PM

git

Noun: git (plural gits)

(Britain, Ireland, Australia, New Zealand, slang, derogatory) A silly, incompetent, stupid, or annoying person (usually a man).
2000 December 18, BBC and Bafta Tribute to Michael Caine, 16:43–17:05:
Parkinson: You made films before, but the part that really made your name was Zulu, wasn't it […] and there of course—against type—you played the toff, you played the officer.
Caine: I played the officer, yeah, and everybody thought I was like that. Everyone was so shocked when they met me, this like Cockney guy had played this toffee-nosed git.

4:34 PM

ah ok, I got that. But it was bit of a stretch in that context

4:48 PM

Give Charlie a break, he’s English

A: How to redirect read queries transparently to Secondary replica in Always on

Can you please tell us, is there a way to implement redirecting read only queries to secondary replica transparently ? Natively in SQL Server AlwaysOn Availability Groups, I don't believe so. The thing is, a connection needs to be established first before a query can be ran. Once the connection...

@J.D. You can't disable the login on the primary, because the driver initially connects to the primary, and then SQL Server responds with the address of the secondary to connect to, and the driver disconnects from the primary and reconnects to the appropriate secondary to run its query.

A: Prevent read access on primary AG replica, but allow it on the readable secondary

It seems we basically need to allow a login connect permission on the primary replica [...] That's exactly right. The login being used to connect has to be able to connect to the primary. When you using ApplicationIntent=ReadOnly in a connection string that's pointed at an AG listener, the...

I'm tempted to say those two questions are duplicates.

Peter Vandivier

5:04 PM

but also that's what git is named after https://en.wikipedia.org/wiki/Git#Naming

> Torvalds sarcastically quipped about the name git (which means "unpleasant person" in British English slang): "I'm an egotistical bastard, and I name all my projects after myself. First 'Linux', now 'git'."

@Charlieface needs more invoices

@PeterVandivier I'll definitely agree with him on that one.

2 hours later…

7:13 PM

@JoshDarnell just seeing this now. Oh that's interesting. Idk why I feel like I've heard that advice before in an AOAG topology. So the listener actually always communicates with the primary first regardless?

Hannah Vernon

yes, SSMS, yes

lol

~~expensive~~

how much is that query in the window

Hannah Vernon

ruff ruff

1 hour later…

8:36 PM

@J.D. When you connect via the listener, you always go to the primary first, yes.

Also, all the cool kids just say AG. Not AOAG.

@JoshDarnell ah ok thanks, I'll revise my answer when I get a chance. That's good to know.

@JoshDarnell yea but we both know I'm not cool. 😉

Btw this feels like a silly question, but is there some kind of syntax to create a temp table from a values list? E.g. SELECT VALUES (1), (2), (3) AS SomeColumn INTO #SomeTempTable;

SELECT * INTO #test FROM ( VALUES ('a', 2)) v (t, b)

@Zikato thank you! It's not one I often need to do, but I knew there'd be a way.

but if you do that in production code, I'll haunt you

Lol why?

8:48 PM

I dislike the SELECT INTO clause. I prefer explicitly declared TempTables.

@J.D. Haha!

fewer hurdles to get a fully parallel insert with select into

@Zikato ah gotcha. Remind me to never show you any of my production code where I never explicitly create my temp tables lol.

implicitly created temp table - you usually can't generate an estimated plan until the temp table exists. No not null constraints. Data type inference sucks on expressions. People usually add indexes afterwards and prevent temp table caching

You guys make compelling arguments to reevaluate my life I guess...

But at least you're cool with me using the VALUES keyword. I initially thought that's where the beef would be. lol

8:51 PM

It's a table valued constructor, what would be wrong with that?

@Zikato i know it's not the same, but you can mark columns not null by wrapping them in isnull with select into

oOo

@ErikDarling Interesting, never tried that

twitter.com/Zikato25/status/1637919944284176384

Heap™ copy for @SeanGallardy

@Zikato i blog

You blog more then everyone else in SQL community combined. It's hard to keep up

9:05 PM

i should probably stop and enjoy life

like paul

drinking champagne in jandals all day

Well, I'm certainly grateful that you do. But at the same time I wonder if there are diminishing returns or if the paid training offers more than what's already available for free. And I suppose it's good for the SEO

there is more to the paid training, but i have been pushing out some of what's included in it to get more people in the door

i plan on redoing all of it soon, since 2022 has some additional stuff to talk about on top of what's there

everything was recorded using 2019, so there are enough differences

9:35 PM

@Zikato I concur, it's also annoying when DROP statements are involved, due to batch parsing. On the rare occasions I use them, I just do a IF OBJECT_ID... IS NULL CREATE and never use DROP. And I declare the indexes inline. Yes, can cause problems with huge inserts, but I'm rarely doing that in prod as an automated proc, only by hand.

9:47 PM

at least no one brought up CTEs

Paul White

9:57 PM

Autumn equinox today. Twenty minutes ago, in fact

10:31 PM

What did you do to celebrate

Paul White

Drank champagne in jandals