X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EF8FD52849 for ; Tue, 21 Jun 2005 16:10:26 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 82841-07 for ; Tue, 21 Jun 2005 19:10:24 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.204]) by svr1.postgresql.org (Postfix) with ESMTP id 0FDD652952 for ; Tue, 21 Jun 2005 16:10:18 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id 36so1722679wra for ; Tue, 21 Jun 2005 12:10:19 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type; b=upUHFBdQo3O1XSLFnek3MhqNHhigkVh1k7gRUwpso7bFhCn30lLTYLwttYgo3W4p0pie3bY+xHQ++fYf5zU/wMAcBn66c1kUNeXn5XXutuolZswdOu2rwSbKfLar9cmT/cYQh5DVDcBTAS1MHsYAFhQA16SpSeaXkCZqyI67JaA= Received: by 10.54.53.25 with SMTP id b25mr3368364wra; Tue, 21 Jun 2005 12:09:49 -0700 (PDT) Received: by 10.54.104.3 with HTTP; Tue, 21 Jun 2005 12:09:48 -0700 (PDT) Message-ID: Date: Tue, 21 Jun 2005 21:09:48 +0200 From: Kjell Tore Fossbakk Reply-To: Kjell Tore Fossbakk To: pgsql-performance@postgresql.org Subject: Querying 19million records very slowly Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3018_21547389.1119380988566" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.781 tagged_above=0 required=5 tests=AWL, HTML_20_30, HTML_MESSAGE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200506/406 X-Sequence-Number: 13043 ------=_Part_3018_21547389.1119380988566 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hello! I use FreeBSD 4.11 with PostGreSQL 7.3.8. I got a huge database with roughly 19 million records. There is just one table, with a time field, a few ints and a few strings. table test fields time (timestamp), source (string), destination (string), p1 (int), p2 (int) I have run VACUUM ANALYZE ; I have created indexes on every field, but for some reason my postgre server wants to use a seqscan, even tho i know a indexed scan would be much faster. create index test_time_idx on test (time) ; create index test_source_idx on test (source) ; create index test_destination_idx on test (destination) ; create index test_p1_idx on test (p1) ; create index test_p2_idx on test (p2) ; What is really strange, is that when i query a count(*) on one of the int fields (p1), which has a very low count, postgre uses seqscan. In another count on the same int field (p1), i know he is giving about 2.2 million hits, but then he suddenly uses seqscan, instead of a indexed one. Isn't the whole idea of indexing to increase performance in large queries.. To make sort of a phonebook for the values, to make it faster to look up what ever you need... This just seems opposite.. Here is a EXPLAIN of my query database=3D> explain select date_trunc('hour', time),count(*) as total from test where p1=3D53 and time > now() - interval '24 hours' group by date_trunc order by date_trunc ; QUERY PLAN ---------------------------------------------------------------------------= --------------- Aggregate (cost=3D727622.61..733143.23 rows=3D73608 width=3D8) -> Group (cost=3D727622.61..731303.02 rows=3D736083 width=3D8) -> Sort (cost=3D727622.61..729462.81 rows=3D736083 width=3D8) Sort Key: date_trunc('hour'::text, "time") -> Seq Scan on test (cost=3D0.00..631133.12 rows=3D736083 width=3D8) Filter: ((p1 =3D 53) AND ("time" > (now() - '1 day'::interval))) (6 rows) database=3D> drop INDEX test_ test_source_idx test_destination_idx test_p1_idx=20 test_p2_idx test_time_idx After all this, i tried to set enable_seqscan to off and enable_nestedloops to on. This didnt help much either. The time to run the query is still in minutes. My results are the number of elements for each hour, and it gives about 1000-2000 hits per hour. I have read somewhere, about PostGreSQL, that it can easily handle 100-200million records. And with the right tuned system, have a great performance.. I would like to learn how :) I also found an article on a page (http://techdocs.postgresql.org/techdocs/pgsqladventuresep3.php): Tip #11: Don't bother indexing columns with huge numbers of records and a small range of values, such as BOOLEAN columns. This tip, regretfully, is perhaps the only tip where I cannot provide a good, real-world example from my work. So I'll give you a hypothetical situation instead: Imagine that you have a database table with a list of every establishment vending ice cream in the US. A simple example might look like: Where there were almost 1 million rows, but due to simplistic data entry, only three possible values for type (1-SUPERMARKET, 2-BOUTIQUE, and 3-OTHER) which are relatively evenly distributed. In this hypothetical situation, you might find (with testing using EXPLAIN) that an index on type is ignored and the parser uses a "seq scan" (or table scan) instead.= =20 This is because a table scan can actually be faster than an index scan in this situation. Thus, any index on type should be dropped. Certainly, the boolean column (active) requires no indexing as it has only two possible values and no index will be faster than a table scan. Then I ask, what is useful with indexing, when I can't use it on a VERY large database? It is on my 15 million record database it takes for ever to do seqscans over and over again... This is probably why, as i mentioned earlier, the reason (read the quote) why he chooses a full scan and not a indexed one... So what do I do? :confused: I'v used SQL for years, but never in such a big scale. Thus, not having to learn how to deal with large number of records. Usually a maximum of 1000 records. Now, with millions, I need to learn a way to make my sucky queries better. Im trying to learn more about tuning my system, makeing better queries and such. I'v found some documents on the Internet, but far from the best. Feedback most appreciated! Regards, a learning PostGreSQL user ------=_Part_3018_21547389.1119380988566 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hello!

I use FreeBSD 4.11 with PostGreSQL 7.3.8.

I got a huge= database with roughly 19 million records. There is just one
table, with= a time field, a few ints and a few strings.

table test
field= s time (timestamp), source (string), destination (string), p1 (int),
p2 (int)

I have run VACUUM ANALYZE ;

I have created i= ndexes on every field, but for some reason my postgre
server wants to us= e a seqscan, even tho i know a indexed scan would be
much faster.

create index test_time_idx on test (time) ;
create index test_so= urce_idx on test (source) ;
create index test_destination_idx on test (d= estination) ;
create index test_p1_idx on test (p1) ;
create index te= st_p2_idx on test (p2) ;

What is really strange, is that when i query a count(*) on = one of the int
fields (p1), which has a very low count, postgre uses seq= scan. In another
count on the same int field (p1), i know he is giving a= bout=20 2.2 million
hits, but then he suddenly uses seqscan, instead of a indexe= d one. Isn't
the whole idea of indexing to increase performance in large= queries.. To
make sort of a phonebook for the values, to make it faster= to look up what
ever you need... This just seems opposite..

Here is a EXPLAIN of= my query

database=3D> explain select date_trunc('hour', time),co= unt(*) as total from
test where p1=3D53 and time > now() - interval '= 24 hours' group by
date_trunc order by date_trunc ;
      = ;            &n= bsp;            = ;         QUERY PLAN
-----------= ---------------------------------------------------------------------------= ----
Aggregate (cost=3D727622.61..733143.23 rows=3D73608 width=3D8)
   -> Group (cost=3D72= 7622.61..731303.02 rows=3D736083 width=3D8)
    &nbs= p;    -> Sort (cost=3D727622.61..729462.81 ro= ws=3D736083 width=3D8)
        &= nbsp;      Sort Key: date_trunc('hour'::text, &quo= t;time")
            = ;   -> Seq Scan on test (cost=3D0.00..631133.12 ro= ws=3D736083
width=3D8)
       &nbs= p;             = Filter: ((p1 =3D 53) AND ("time" > (now() - '1
day'::interv= al)))
(6 rows)

database=3D> drop INDEX test_<TABULATOR>
test_source_idx &= nbsp;       test_destination_idx  &= nbsp;     test_p1_idx     &nbs= p;
test_p2_idx       test_time_idx<= br>

After all this, i tried to set enable_seqscan to off and
enable_nestedloops to on. This didnt help much either. The time to run = the
query is still in minutes. My results are the number of elements for= each
hour, and it gives about 1000-2000 hits per hour. I have read some= where,
about PostGreSQL, that it can easily handle 100-200million records. And=
with the right tuned system, have a great performance.. I would like to=
learn how :)

I also found an article on a page
( http://techdocs.postgresql.org/techdocs/pgsqladventuresep3.php):
Tip= #11: Don't bother indexing columns with huge numbers of records and = a
small range of values, such as BOOLEAN columns.

This tip, regre= tfully, is perhaps the only tip where I cannot provide a
good, real-world example from my work. So I'll give you a hypothe= tical
situation instead:

Imagine that you have a database table w= ith a list of every establishment
vending ice cream in the US. A s= imple example might look like:

Where there were almost 1 million rows, but due to simplistic data = entry,
only three possible values for type (1-SUPERMARKET, 2-BOUTIQUE, a= nd
3-OTHER) which are relatively evenly distributed. In this hypot= hetical
situation, you might find (with testing using EXPLAIN) that an index on=
type is ignored and the parser uses a "seq scan" (or table sc= an) instead.
This is because a table scan can actually be faster than a= n index scan in
this situation. Thus, any index on type should be dropped.
Certainly, the boolean column (active) requires no indexing as it has only=
two possible values and no index will be faster than a table scan.
<= br>
Then I ask, what is useful with indexing, when I can't use it on a VERY=
large database? It is on my 15 million record database it takes for eve= r
to do seqscans over and over again... This is probably why, as i menti= oned
earlier, the reason (read the quote) why he chooses a full scan and not= a
indexed one...

So what do I do? :confused:

I'v used SQL= for years, but never in such a big scale. Thus, not having to
learn how= to deal with large number of records. Usually a maximum of 1000
records. Now, with millions, I need to learn a way to make my sucky
= queries better.

Im trying to learn more about tuning my system, make= ing better queries and
such. I'v found some documents on the Internet, b= ut far from the best.

Feedback most appreciated!

Regards,
a learning PostGreSQL= user

------=_Part_3018_21547389.1119380988566--