Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 3A07A632B9F for ; Thu, 7 Jan 2010 14:11:48 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 42196-05 for ; Thu, 7 Jan 2010 18:11:37 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.152]) by mail.postgresql.org (Postfix) with ESMTP id 11D0B632284 for ; Thu, 7 Jan 2010 14:11:36 -0400 (AST) Received: by fg-out-1718.google.com with SMTP id 19so7610346fgg.1 for ; Thu, 07 Jan 2010 10:11:35 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :from:date:message-id:subject:to:cc:content-type; bh=wc12mS8tLhuykL8GCrqw7GJU6iv4qNvfiGV/L2WZlsw=; b=IJJvQIvHVOqP/qz6M4p2bYf02/xE1tpvTXn8u/PXMbAdDGaqGq+8LdvkDRuEZaYc9x QVm+/TUqkPjcMBAY6aEO3MF5JmQBgfZuZZGOZoohtQUKw+SOd/eM5STF08tLwk0kZBvC 9IP/43lv5uaMXGSx61xXKmmIR/+LYdMQf/4xw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; b=TxXj7Lcyeaw+sQOECnGkXkDIhn0m7XJicjxhaas/0qmlYeIv9q5+/Dgn/AkYTuRLUR lbXQBw+6GU0iDirtQe7PiFb3GEo8lcW2uCIhjDYnrs5zVbqEq0x1XK9hicYRnnsJk0yF Xpdnm36/q6MGbLxnQN8es9QW4o/qtFTBRp8uw= MIME-Version: 1.0 Received: by 10.216.88.143 with SMTP id a15mr3351694wef.206.1262887895408; Thu, 07 Jan 2010 10:11:35 -0800 (PST) In-Reply-To: <27470544.48331262887021660.JavaMail.root@mail.4linux.com.br> References: <21301378.46621262884592029.JavaMail.root@mail.4linux.com.br> <27470544.48331262887021660.JavaMail.root@mail.4linux.com.br> From: Nikolas Everett Date: Thu, 7 Jan 2010 13:11:15 -0500 Message-ID: Subject: Re: Air-traffic benchmark To: "Gurgel, Flavio" Cc: Matthew Wakeling , Lefteris , pgsql-performance@postgresql.org, Ivan Voras Content-Type: multipart/alternative; boundary=0016e6d6454e65a368047c970021 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.079 tagged_above=-10 required=5 tests=BAYES_05=-1.11, FH_DATE_PAST_20XX=3.188, HTML_MESSAGE=0.001 X-Spam-Level: ** X-Archive-Number: 201001/114 X-Sequence-Number: 36994 --0016e6d6454e65a368047c970021 Content-Type: text/plain; charset=ISO-8859-1 This table is totally unnormalized. Normalize it and try again. You'll probably see a huge speedup. Maybe even 10x. My mantra has always been less data stored means less data to scan means faster scans. On Thu, Jan 7, 2010 at 12:57 PM, Gurgel, Flavio wrote: > ----- "Matthew Wakeling" escreveu: > > On Thu, 7 Jan 2010, Gurgel, Flavio wrote: > > Postgres does not change a query plan according to the shared_buffers > > > > setting. It does not anticipate one step contributing to another step > > in > > this way. It does however make use of the effective_cache_size setting > > to > > estimate this effect, and that does affect the planner. > > That was what I was trying to say :) > > > In a situation like this, the opposite will be true. If you were > > accessing > > a very small part of a table, say to order by a field with a small > > limit, > > then an index can be very useful by providing the results in the > > correct > > order. However, in this case, almost the entire table has to be read. > > > > Changing the order in which it is read will mean that the disc access > > is > > no longer sequential, which will slow things down, not speed them up. > > > > The Postgres planner isn't stupid (mostly), there is probably a good > > reason why it isn't using an index scan. > > Sorry but I disagree. This is the typical case where the test has to be > made. > The results are partial, let's say 50% of the table. Considerind that the > disk is fast enough, the cost estimation of sequential and random reads are > in a proportion of 1 to 4, considering default settings in PostgreSQL. > > If, and only if the data is time distributed in the table (which can be > this case during bulk load) there will be some gain in seqscan. > If, let's say 50% of the 50% (25% of the data) is time distributed (which > can be the case in most data warehouses), the cost of random reads * number > of reads can be cheaper then seqscan. > > The volume of data doesn't turn the index generations so deep, let's say > 2^7 or 2^8. This can lead to very fast data retrieval. > > > > Ugh. I don't think so. That's why indexes were invented. PostgreSQL > > is > > > smart enough to "jump" over columns using byte offsets. > > > A better option for this table is to partition it in year (or > > year/month) chunks. > > > > Postgres (mostly) stores the columns for a row together with a row, so > > > > what you say is completely wrong. Postgres does not "jump" over > > columns > > using byte offsets in this way. The index references a row in a page > > on > > disc, and that page is fetched separately in order to retrieve the > > row. > > The expensive part is physically moving the disc head to the right > > part of > > the disc in order to fetch the correct page from the disc - jumping > > over > > columns will not help with that at all. > > If the index point to the right pages, I keep the circumstance of 1 to 4 > cost. > Agreed about seqscans. When I talked about byte offsets I was talking of > data in the same disk page, and this does not help I/O reduction at all. > > > Reducing the width of the table will greatly improve the performance > > of a > > sequential scan, as it will reduce the size of the table on disc, and > > > > therefore the time taken to read the entire table sequentially. > > I just don't understand if you're seeing this situation as OLTP or DW, > sorry. > DW tables are usually wider then OLTP. > > > Moreover, your suggestion of partitioning the table may not help much > > with > > this query. It will turn a single sequential scan into a UNION of many > > > > tables, which may be harder for the planner to plan. Also, for queries > > Partitioned plans are a collection of an independent plan for each table in > the inheritance. > If the data to be retrieved is confined in selected partitions, you won't > seqscan the partitions you don't need. > The cost of the "union" of the aggregations in memory is a lot cheaper then > the avoided seqscans. > > I have at least 3 cases of partitioning in queries exactly like this that > droped from 3min to 5s execution times. > All of that DW tables, with aggregation and huge seqscans. > > I keep my word that the right use of indexes here has to be tested. > > Flavio Henrique A. Gurgel > tel. 55-11-2125.4786 > cel. 55-11-8389.7635 > www.4linux.com.br > FREE SOFTWARE SOLUTIONS > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > --0016e6d6454e65a368047c970021 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable This table is totally unnormalized. =A0Normalize it and try again. =A0You&#= 39;ll probably see a huge speedup. =A0Maybe even 10x. =A0My mantra has alwa= ys been less data stored means less data to scan means faster scans.
On Thu, Jan 7, 2010 at 12:57 PM, Gurgel, Flavio <flavio@4linux.com.br> wrot= e:
----- "Matthew Wakeling" <matthew@flymine.org> escreveu:
> On Thu, 7 Jan 2010, Gurgel, Flavio wrote:
> Postgres does not change a query plan accordin= g to the shared_buffers
>
> setting. It does not anticipate one step contributing to another step<= br> > in
> this way. It does however make use of the effective_cache_size setting=
> to
> estimate this effect, and that does affect the planner.

That was what I was trying to say :)

> In a situation like this, the opposite will be true. If you were
> accessing
> a very small part of a table, say to order by a field with a small
> limit,
> then an index can be very useful by providing the results in the
> correct
> order. However, in this case, almost the entire table has to be read.<= br> >
> Changing the order in which it is read will mean that the disc access<= br> > is
> no longer sequential, which will slow things down, not speed them up.<= br> >
> The Postgres planner isn't stupid (mostly), there is probably a go= od
> reason why it isn't using an index scan.

Sorry but I disagree. This is the typical case where the test has to = be made.
The results are partial, let's say 50% of the table. Considerind that t= he disk is fast enough, the cost estimation of sequential and random reads = are in a proportion of 1 to 4, considering default settings in PostgreSQL.<= br>
If, and only if the data is time distributed in the table (which can be thi= s case during bulk load) there will be some gain in seqscan.
If, let's say 50% of the 50% (25% of the data) is time distributed (whi= ch can be the case in most data warehouses), the cost of random reads * num= ber of reads can be cheaper then seqscan.

The volume of data doesn't turn the index generations so deep, let'= s say 2^7 or 2^8. This can lead to very fast data retrieval.

> > Ugh. I don't think so. That's why indexes were invented. = PostgreSQL
> is
> > smart enough to "jump" over columns using byte offsets.=
> > A better option for this table is to partition it in year (or
> year/month) chunks.
>
> Postgres (mostly) stores the columns for a row together with a row, so=
>
> what you say is completely wrong. Postgres does not "jump" o= ver
> columns
> using byte offsets in this way. The index references a row in a page > on
> disc, and that page is fetched separately in order to retrieve the
> row.
> The expensive part is physically moving the disc head to the right
> part of
> the disc in order to fetch the correct page from the disc - jumping > over
> columns will not help with that at all.

If the index point to the right pages, I keep the circumstance of 1 t= o 4 cost.
Agreed about seqscans. When I talked about byte offsets I was talking of da= ta in the same disk page, and this does not help I/O reduction at all.

> Reducing the width of the table will greatly improve the performance > of a
> sequential scan, as it will reduce the size of the table on disc, and<= br> >
> therefore the time taken to read the entire table sequentially.

I just don't understand if you're seeing this situation as OL= TP or DW, sorry.
DW tables are usually wider then OLTP.

> Moreover, your suggestion of partitioning the table may not help much<= br> > with
> this query. It will turn a single sequential scan into a UNION of many=
>
> tables, which may be harder for the planner to plan. Also, for queries=

Partitioned plans are a collection of an independent plan for each ta= ble in the inheritance.
If the data to be retrieved is confined in selected partitions, you won'= ;t seqscan the partitions you don't need.
The cost of the "union" of the aggregations in memory is a lot ch= eaper then the avoided seqscans.

I have at least 3 cases of partitioning in queries exactly like this that d= roped from 3min to 5s execution times.
All of that DW tables, with aggregation and huge seqscans.

I keep my word that the right use of indexes here has to be tested.

Flavio Henrique A. Gurgel
tel. 55-11-2125.4786
cel. 55-11-8389.7635
ww= w.4linux.com.br
FREE SOFTWARE SOLUTIONS

--
Sent via pgsql-performance mailing = list (pgsql-performance= @postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

--0016e6d6454e65a368047c970021--