Skip site navigation (1) Skip section navigation (2)

Re: Index with all necessary columns - Postgres vs MSSQL

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Gudmundur Johannesson <gudmundur(dot)johannesson(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Index with all necessary columns - Postgres vs MSSQL
Date: 2012-02-01 19:35:35
Message-ID: CAHyXU0zwC8bP094Obu8g0wDzGX66ZpA5NJByvEGsfKw2Ck70Vg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, Feb 1, 2012 at 12:50 PM, Gudmundur Johannesson
<gudmundur(dot)johannesson(at)gmail(dot)com> 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 is about 23 seconds.

what's the difference between the first and the second run time?
Note, if you are only interested in the date the dtStamp falls on, you
can exploit that in the index to knock 4 bytes off your index entry:

CREATE INDEX test_all
  ON test
  USING btree
  (id , (dtstamp::date) , rating);

and then use a similar expression to query it back out.

> 3) The query takes 23 sec vs 1 sec or lower in mssql.

I asked you to time a different query.  Look again (and I'd like to
see cached and uncached times).

> We never update/delete and therefore the data is alway correct in the index
> (never dirty).  Therefore, Postgres could have used the data in it.
>
> I started to add columns into indexes in Oracle for approx 15 years ago and
> it was a brilliant discovery.  This looks like a show stopper for me but I

I doubt covering indexes is going to make that query 23x faster.
However, I bet we can get something worked out.

merlin

In response to

Responses

pgsql-performance by date

Next:From: Merlin MoncureDate: 2012-02-02 14:52:17
Subject: Re: From Simple to Complex
Previous:From: Alessandro GagliardiDate: 2012-02-01 19:19:27
Subject: Re: From Simple to Complex

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group