Thread: firebird X postgresql 8.1.2 windows, performance comparison
Hello, Attached is the text file containing the last rounds of configurations. This time, used "show all" just before issuing each relevant "explain analyze" to ensure available information. Note that the last runs are being executed concurrently with other problematic query that is consuming 100% cpu for HOURS. Some people suggested to reduce shared buffers, but for few users (1 or 2 simultaneously for this app, as my friend told me), it could be large. http://candle.pha.pa.us/main/writings/pgsql/hw_performance/ brought some light over the subject. For few users, could be a viable alternative. But, despite these huge improvements in speed, there are other problematic queries with postgresql. One of them is: select count(distinct NF.ID_NF ) as contagem, DE.AM_REFERENCIA as campo from DECLARACAO DE inner join CADASTRO CAD on (CAD.ID_DECLARACAO=DE.ID_DECLARACAO) inner join NOTA_FISCAL NF on (NF.ID_CADASTRO=CAD.ID_CADASTRO) inner join EMPRESA EMP on (EMP.ID_EMPRESA=DE.ID_EMPRESA) inner join ARQUIVO_PROCESSADO ARQ on (ARQ.ID_ARQUIVO=DE.ID_ARQUIVO) group by DE.AM_REFERENCIA order by DE.AM_REFERENCIA firebird windows executed in 1min30s postgresql windows is running for 3 hours and still not finished. I already know that count() is VERY performance problematic in postgresql. Is there a way to work around this? Unfortunately, the deadline for my friend project is approaching and he is giving up postgresql for firebird. If some work around is available, he will give another try. But i already saw that count and joins are still problem. He asked me if other people are struggling with poor performance and wondered if all other users are issuing simple queries only. Any suggestions? Thanks . Andre Felipe Machado
Attachment
Andre, > http://candle.pha.pa.us/main/writings/pgsql/hw_performance/ > brought some light over the subject. For few users, could be a viable > alternative. That article is very old. Read this instead: http://www.powerpostgresql.com/PerfList > select count(distinct NF.ID_NF ) as contagem, DE.AM_REFERENCIA as campo > from DECLARACAO DE inner join CADASTRO CAD on > (CAD.ID_DECLARACAO=DE.ID_DECLARACAO) > inner join NOTA_FISCAL NF on (NF.ID_CADASTRO=CAD.ID_CADASTRO) > inner join EMPRESA EMP on (EMP.ID_EMPRESA=DE.ID_EMPRESA) > inner join ARQUIVO_PROCESSADO ARQ on (ARQ.ID_ARQUIVO=DE.ID_ARQUIVO) > group by DE.AM_REFERENCIA > order by DE.AM_REFERENCIA > > firebird windows executed in 1min30s > postgresql windows is running for 3 hours and still not finished. How about an EXPLAIN? And, did you run ANALYZE on the data? > I already know that count() is VERY performance problematic in > postgresql. Is there a way to work around this? > Unfortunately, the deadline for my friend project is approaching and he > is giving up postgresql for firebird. > If some work around is available, he will give another try. But i > already saw that count and joins are still problem. > He asked me if other people are struggling with poor performance and > wondered if all other users are issuing simple queries only. No, actually we excel at complex queries. Some of the data warehousing stuff I run involves queries more than a page long. Either you're hitting some Windows-specific problem, or you still have some major basic tuning issues. That being said, there's nothing wrong with Firebird if he wants to use it. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
On Mon, 2006-03-13 at 12:11, andremachado wrote: > Hello, > Attached is the text file containing the last rounds of configurations. > This time, used "show all" just before issuing each relevant "explain analyze" > to ensure available information. > Note that the last runs are being executed concurrently with other problematic > query that is consuming 100% cpu for HOURS. > Some people suggested to reduce shared buffers, but for few users (1 or 2 > simultaneously for this app, as my friend told me), it could be large. > > http://candle.pha.pa.us/main/writings/pgsql/hw_performance/ > brought some light over the subject. For few users, could be a viable alternative. > > But, despite these huge improvements in speed, there are other problematic > queries with postgresql. > One of them is: > > select count(distinct NF.ID_NF ) as contagem, DE.AM_REFERENCIA as campo > from DECLARACAO DE inner join CADASTRO CAD on > (CAD.ID_DECLARACAO=DE.ID_DECLARACAO) > inner join NOTA_FISCAL NF on (NF.ID_CADASTRO=CAD.ID_CADASTRO) > inner join EMPRESA EMP on (EMP.ID_EMPRESA=DE.ID_EMPRESA) > inner join ARQUIVO_PROCESSADO ARQ on (ARQ.ID_ARQUIVO=DE.ID_ARQUIVO) > group by DE.AM_REFERENCIA > order by DE.AM_REFERENCIA > > firebird windows executed in 1min30s > postgresql windows is running for 3 hours and still not finished. > > I already know that count() is VERY performance problematic in postgresql. > Is there a way to work around this? Well, it's not uncommon in mvcc databases. My testing against Oracle 9.x series showed little difference on similar machines. In fact, my workstation running PostgreSQL was faster at count() queries than our old Sun 420 running Oracle, which has much more memory. Can we see an explain output and schema (if needed) for this query? Just plain explain, not analyze, since, like you said, it's been running for hours. I'd like to just add, that if you use any database long enough, you'll eventually come up with queries that it runs slow on that other databases run quickly on. It's just the nature of the beast. That said, I've never seen a team work so hard to fix poorly performing queries as the guys that write PostgreSQL. If there's a natural, basic fix to the problem, you'll see it pretty quick, whether that be in the query itself, the planner, or the execution of the query. And if it's just not possible in PostgreSQL, you'll usually hear that pretty quick too.