Re: nested loop semijoin estimates

On 06/02/15 23:27, Tom Lane wrote:
> I wrote:
>> Hm. In principle, add_path_precheck shouldn't be doing anything except
>> rejecting paths that would get rejected anyway later on. However it
>> strikes me that there's a logic error in it; specifically, this
>> simplifying assumption is wrong:
>> * For speed, we make exact rather than fuzzy cost comparisons. If an
>> * old path dominates the new path exactly on both costs, it will
>> * surely do so fuzzily.
>
> After staring at this awhile longer, I still don't see exactly why
> it's changing the results.

So the required_outer vs. (!consider_startup) inconsistency does not
explain that?

> Do we have instructions around here anyplace on how to set up/use
> TPC-DS? I couldn't find anything about it on the wiki ...

Not that I'm aware of, but it's not really all that difficult.

1) get the TPC-DS toolkit from

http://www.tpc.org/information/current_specifications.asp

2) build the 'tools' directory (just "make")

3) generate data "dsdgen -scale 1" (1GB dataset)

4) fix the data file formatting (pipe at the end)

for f in *.dat; do sed 's/|$//g' $f > ${f/dat/csv}; done;

5) create database structure (attached 'create.sql')

6) load data (attached 'load.sql', fix paths first)

7) indexes.sql

8) run queries.sql or explain.sql

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services