-1

As you can see I'm a newbie and been flailing for 2 weeks.

I am doing a simple query such as: "select * from all_visits where reason_for_visit in ('CE233','4E233','GH399')

This query in Postgres takes 2 hours, and I give up as I don't have time to wait (I'm doing some case control studies and doing some exploratory basic research).

This exact same query in SQL server 2014 takes about 30 minutes or less. I've tried these queries on the same machine and Postgres is faster (8gb ram, i5 processor).

I would much rather use Postgresql, over SQLServer, but not if the performance hit is this bad.

What am I doing wrong? I have no reason to believe that SQL Server is more 'ready to go' out of the box than Postgres, or is it?

I have increased the memory buffer to 4gb and the effective cache to 6gb but that's all I know how to do. Would appreciate some help as well as insight.

closed as too broad by David Spillett, Daniel Vérité, Mikael Eriksson, Colin 't Hart, Michael Green Mar 17 '15 at 11:18

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

1

Without much more detail we can only guess (and your question is likely to be closed as too broad or unclear). You don't say if all_visits is a table or a view, what the table structure is (or view definition and supporting table structures if it is a view) particularly what indexes and keys are defined.

The most obvious thing to check is verify that the appropriate keys/indexes are defined in both databases (particularly any covering reason_for_visit if all_visits is a plain table and any clustering key too).

Also the query planners may have different optimisations for that sort of query. I doubt that would have a massive effect for a simple query like that over a plain table but if all_visits is a complex view there could be any number of differences effects due to the two database engines not handling some conditions identically.

If the issue is due to query plan differences and all else is equal, it may be they are handling the IN clause differently (maybe one is scanning between the outer values but the other is seeking the three individually?). Try running

select * from all_visits where reason_for_visit ='CE233';
select * from all_visits where reason_for_visit ='4E233';
select * from all_visits where reason_for_visit ='GH399';

on both and see if there is a difference searching for individual values.

As an aside: is there a particular reason why you'd prefer postgres for this task? Familiarity with the engine/tools? Preference for F/OSS? Features you might want now or later available in postgres but not the edition of SQL Server you have access to? If SQL Server is working well for the task then both are equally free (though not Free, of course) assuming you are talking about SQL Server Express.

  • wiki.postgresql.org/wiki/Slow_Query_Questions and altering the .conf file sped things up x 10. Reference: shared_buffers should be 10% to 25% of available RAM effective_cache_size should be 75% of available RAM Test changing work_mem: increase it to 8MB, 32MB, 256MB, 1GB. Does it make a difference? HECK YEAH, made a huge difference. 10 fold speed up. And postgres is now faster than sqlserver for the same query! – user798719 Mar 17 '15 at 15:05

Not the answer you're looking for? Browse other questions tagged or ask your own question.