[PERFORM] Index with all necessary columns - Postgres vs MSSQL

2012-02-01 Thread Gudmundur Johannesson
Hi, I have a table in Postgres like: CREATE TABLE test ( id integer, dtstamp timestamp without time zone, rating real ) CREATE INDEX test_all ON test USING btree (id , dtstamp , rating); My db has around 200M rows and I have reduced my test select statement down to: SELECT count(1) FR

Re: [PERFORM] Index with all necessary columns - Postgres vs MSSQL

2012-02-01 Thread Merlin Moncure
On Wed, Feb 1, 2012 at 11:10 AM, Gudmundur Johannesson wrote: > Hi, > > I have a table in Postgres like: > CREATE TABLE test > ( >   id integer, >   dtstamp timestamp without time zone, >   rating real > ) > CREATE INDEX test_all >   ON test >   USING btree >   (id , dtstamp , rating); > > My db h

Re: [PERFORM] From Simple to Complex

2012-02-01 Thread Alessandro Gagliardi
Final update on this thread: since it is only necessary for me to get a rough ratio of the distribution (and not the absolute count), I refactored the query to include a subquery that samples from the moments table thus: SELECT moment_id, block_id FROM moments WHERE inserted BETWEEN 'yesterday' AND

Re: [PERFORM] Index with all necessary columns - Postgres vs MSSQL

2012-02-01 Thread Scott Marlowe
On Wed, Feb 1, 2012 at 10:10 AM, Gudmundur Johannesson wrote: > Hi, > > I have a table in Postgres like: > CREATE TABLE test > ( >   id integer, >   dtstamp timestamp without time zone, >   rating real > ) > CREATE INDEX test_all >   ON test >   USING btree >   (id , dtstamp , rating); > > My db h

Re: [PERFORM] From Simple to Complex

2012-02-01 Thread Scott Marlowe
On Wed, Feb 1, 2012 at 11:19 AM, Alessandro Gagliardi wrote: > Final update on this thread: since it is only necessary for me to get a > rough ratio of the distribution (and not the absolute count), I refactored > the query to include a subquery that samples from the moments table > thus: SELECT m

Re: [PERFORM] From Simple to Complex

2012-02-01 Thread Alessandro Gagliardi
LIMIT 65536; Total query runtime: 14846 ms. - http://explain.depesz.com/s/I3E LIMIT 69632: Total query runtime: 80141 ms. - http://explain.depesz.com/s/9hp So it looks like when the limit crosses a certain threshold (somewhere north of 2^16), Postgres decides to do a Seq Scan instead of an Index S

Re: [PERFORM] From Simple to Complex

2012-02-01 Thread Scott Marlowe
On Wed, Feb 1, 2012 at 11:48 AM, Alessandro Gagliardi wrote: > LIMIT 65536; Total query runtime: 14846 ms. > - http://explain.depesz.com/s/I3E > LIMIT 69632: Total query runtime: 80141 ms. > - http://explain.depesz.com/s/9hp > > So it looks like when the limit crosses a certain threshold (somewher

Re: [PERFORM] From Simple to Complex

2012-02-01 Thread Bob Lunney
Possibly.  What does   psql > show work_mem; say? Bob Lunney From: Alessandro Gagliardi To: pgsql-performance@postgresql.org Sent: Wednesday, February 1, 2012 12:19 PM Subject: Re: [PERFORM] From Simple to Complex Final update on this thread: since it is

Re: [PERFORM] From Simple to Complex

2012-02-01 Thread Alessandro Gagliardi
On Wed, Feb 1, 2012 at 11:04 AM, Bob Lunney wrote: > Possibly. What does > > psql > show work_mem; > > say? > > 100MB

Re: [PERFORM] Index with all necessary columns - Postgres vs MSSQL

2012-02-01 Thread Merlin Moncure
On Wed, Feb 1, 2012 at 12:50 PM, Gudmundur Johannesson wrote: > Here are the answers to your questions: > 1) I change the select statement so I am refering to 1 day at a time.  In > that case the response time is similar.  Basically, the data is not in cache > when I do that and the response time