Thread: Re: [GENERAL] PostgreSQL TPC-H test result?
Moving this thread to Performance alias as it might make more sense for folks searching on this topic: Greg Smith wrote: > On Tue, 9 Sep 2008, Amber wrote: > >> I read something from >> http://monetdb.cwi.nl/projects/monetdb/SQL/Benchmark/TPCH/index.html >> saying that PostgreSQL can't give the correct result of the some >> TPC-H queries > > Jignesh Shah at Sun ran into that same problem. It's mentioned > briefly in his presentation at > http://blogs.sun.com/jkshah/entry/postgresql_east_2008_talk_postgresql > on pages 26 and 27. 5 of the 22 reference TCP-H queries (4, 5, 6, 10, > 14) returned zero rows immediately for his tests. Looks like the > MonetDB crew is saying it does that on queries 4,5,6,10,12,14,15 and > that 20 takes too long to run to generate a result. Maybe 12/15/20 > were fixed by changes in 8.3, or perhaps there were subtle errors > there that Jignesh didn't catch--it's not like he did a formal > submission run, was just kicking the tires. I suspect the difference > on 20 was that his hardware and tuning was much better, so it probably > did execute fast enough. I redid a quick test with the same workload on one of my systems with SF 10 which is about 10GB (I hope it comes out properly displayed) Jignesh From Monet (8.3T/8.2.9) Q Time PG8.3.3 Time PG8.2.9 Ratio 1 429.01 510 0.84 2 3.65 54 0.07 3 33.49 798 0.04 4 6.53 Empty 35 (E) 0.19 5 8.45 Empty 5.5(E) 1.54 6 32.84 Empty 172 (E) 0.19 7 477.95 439 1.09 8 58.55 251 0.23 9 781.96 2240 0.35 10 9.03 Empty 6.1(E) 1.48 11 3.57 Empty 25 0.14 12 56.11 Empty 179 (E) 0.31 13 61.01 140 0.44 14 30.69 Empty 169 (E) 0.18 15 32.81 Empty 168 (E) 0.2 16 23.98 115 0.21 17 Did not finish Did not finish 18 58.93 882 0.07 19 71.55 218 0.33 20 Did not finish Did not finish 21 550.51 477 1.15 22 6.21 Did not finish All time is in seconds (sub seconds where availabe) Ratio > 1 means 8.3.3 is slower and <1 means 8.3.3 is faster My take on the results: * I had to tweak the statement of Q1 in order to execute it. (TPC-H kit does not directly support POSTGRESQL statements) * Timings with 8.3.3 and bit of tuning gives much better time overall This was expected (Some queries finish in 7% of the time than what MonetDB reported. From the queries that worked only Q7 & Q21 seem to have regressed) * However Empty rows results is occuring consistently (Infact Q11 also returned empty for me while it worked in their test) Queries: 4,5,6,10,11,12,14,15 (ACTION ITEM: I will start separate threads for each of those queries in HACKERS alias to figure out the problem since it looks like Functional problem to me and should be interesting to hackers alias) * Two queries 17,20 looks like will not finish (I let Q17 to run for 18 hrs and yet it had not completed. As for Q20 I killed it as it was approaching an hour.) (ACTION ITEM: Not sure whether debugging for these queries will go in hackers or perform alias but I will start a thread on them too.) * Looks like bit of tuning is required for Q1, Q7, Q9, Q21 to improve their overall time. Specially understanding if PostgreSQL is missing a more efficient plan for them. (ACTION ITEM: I will start separate threads on performance alias to dig into those queries) I hope to start separate threads for each queries so we can track them easier. I hope to provide explain analyze outputs for each one of them and lets see if there are any problems. Feedback welcome on what you want to see for each threads. Regards, Jignesh -- Jignesh Shah http://blogs.sun.com/jkshah Sun Microsystems,Inc http://sun.com/postgresql
"Jignesh K. Shah" <J.K.Shah@Sun.COM> writes:
> * However Empty rows results is occuring consistently
> (Infact Q11 also returned empty for me while it worked in their test)
> Queries: 4,5,6,10,11,12,14,15
> (ACTION ITEM: I will start separate threads for each of those queries in
> HACKERS alias to figure out the problem since it looks like Functional
> problem to me and should be interesting to hackers alias)
See discussion suggesting that this is connected to misinterpretation of
INTERVAL literals. If TPC-H relies heavily on syntax that we'd get
wrong, then pretty much every test result has to be under suspicion,
since we might be fetching many more or fewer rows than the test
intends.
I've recently committed fixes that I think would cover this, but you'd
really need to compare specific query rowcounts against other DBMSes
to make sure we're all on the same page.
regards, tom lane
On Thu, Sep 11, 2008 at 11:30 PM, Jignesh K. Shah <J.K.Shah@sun.com> wrote: > Moving this thread to Performance alias as it might make more sense for > folks searching on this topic: You should be using DBT-3. Similarly, a scale factor of 10 is pointless. How many data warehouses are only 10GB? Also, it's well-known that MonetDB will quickly fall over when you run a test larger than can fit in memory. In the real benchmark, the minimum scale factor is 100GB; try it and see what you get. If you have the resources and want to compare it to something, compare it with Oracle on the exact same system. If tuned properly, Oracle 10g (Standard Edition with the exact same tables/indexes/queries as Postgres) is ~5-10x faster and Enterprise Edition is ~50-100x faster. To be fair, an Oracle Enterprise Edition configuration for TPC-H uses advanced partitioning and materialized views, both of which Postgres does not support, which makes it an apples-to-oranges comparison. I haven't tried 11g, but I expect it will perform a bit better in this area given several of the enhancements. Also, while it's not widely known, if you wanted to compare systems and don't want to set it all up yourself, Oracle released Oracle-compatible versions of OSDL's Database Test Suite for DBT-2 (TPC-C) and DBT-3 (TPC-H) as part of their Linux Test Kit which can be found at oss.oracle.com. Due to Oracle's license, I can't give you exact timings, but I have confirmed with several other benchmark professionals that the results mentioned above have been confirmed by others as well. To be clear, I'm not trying to bash on PG and I don't want to start a flame-war. I just think that people should be aware of where we stand in comparison to commercial systems and understand that there's quite a bit of work to be done in the VLDB area. Specifically, I don't think we should be striving for great TPC-H performance, but I believe there is some areas we could improve based on it. Similarly, this is an area where a properly-utilized fadvise may show some benefit. As for running the TPC-H on Postgres, you need a default_statistics_target of at least 250. IIRC, the last time I checked (on 8.3), you really needed a statistics target around 400-500. For the most part, the planner is choosing a bad plan for several of the queries. After you resolve that, you'll quickly notice that Postgres' buffer manager design and the lack of a good multi-block read quickly comes into play. The hash join implementation also has a couple issues which I've recently seen mentioned in other threads. Use DBT-3, it will save you quite a few headaches :) -- Jonah H. Harris, Senior DBA myYearbook.com