Generate test data for a given case

I hit this question recently when searching for the details of the BitmapIndexScan implementation in PostgreSQL.

What I liked a lot was a short and clear test case in the appendix, that is able to produce plans really similar to the initially posted one. This is quite a common need in my practice, especially for cases when I'm not allowed to share schema details in public.

Would it be possible to explain how to come up with such a script?

table structure is pretty much clear, but how to choose a proper size for the textual columns?
how to pick starting and ending points for the range generated? I've mostly seen 1..<some big number> ranges and was quite surprised by your's '1234567', '78901234'. Why strings used here?
why you've used 17 as a step?

In fact, I have one case for which I'm not able to produce a test data. It was posted to the pgsql-performance list a while ago: here

testcase postgres

asked Sep 12 '14 at 10:52

Victor
21●1●1●4

edited Sep 14 '14 at 18:10

Markus Winand ♦♦
936●5●11●20

One Answer:

You mean why I took VARCHAR(255)? Just out of habit. If I don't know any obvious limit, I'm using 255.
This question was about telephone numbers. Hence I tried to generate data that looks like phone numbers. In phone numbers, leading zeros may not disappear. Hence a text column.
I don't remember. But I've probably tried a lot before I settled with the one I've posted. It seems that 17 was find to show what I wanted to show. Further, 17 is one of my favorite prime numbers :)

I see. Well, that's really a trial and error effort. I'm sorry, but I don't have any recipie to follow which always yields test-data to reproduce some effect.

answered Sep 14 '14 at 18:11

Markus Winand ♦♦
936●5●11●20