Horton

Thread

  1. POSTGRES DB 3 800 000 rows table, speed up?

    Eugene <evgenius@hot.ee> — 2005-12-27T21:25:37Z

    Hello!
I've managed to import into postgre DB 3 800 000 rows of data (500 MB pure  
CSV ~ 2 GB SQL DB)
It looks like this

"69110784","69111807","US","UNITED  
STATES","ILLINOIS","BLOOMINGTON","40.4758","-88.9894","61701","LEVEL 3  
COMMUNICATIONS INC","DSL-VERIZON.NET"
"69111808","69112831","US","UNITED  
STATES","TEXAS","GRAPEVINE","32.9309","-97.0755","76051","LEVEL 3  
COMMUNICATIONS INC","DSL-VERIZON.NET"
"69112832","69113087","US","UNITED  
STATES","TEXAS","DENTON","33.2108","-97.1231","76201","LEVEL 3  
COMMUNICATIONS INC","DSL-VERIZON.NET"


CREATE TABLE ipdb2
(
     ipFROM int4 NOT NULL,
     ipTO int4 NOT NULL ,
    countrySHORT CHARACTER(2) NOT NULL,
      countryLONG VARCHAR(64) NOT NULL,
     ipREGION VARCHAR(128) NOT NULL,
     ipCITY VARCHAR(128) NOT NULL,
   ipLATITUDE  DOUBLE PRECISION,
     ipLONGITUDE  DOUBLE PRECISION,
     ipZIPCODE VARCHAR(5),
     ipISP VARCHAR(255) NOT NULL,
     ipDOMAIN VARCHAR(128) NOT NULL

);


I've indexed first two columns they are IPfrom, IPto also table is btree
version of postgre is 7.4.8, on hosting
I ask db like this  SELECT * FROM ipdb2 WHERE '3229285376' BETWEEN ipfrom  
AND ipto;

and get answer after 3-10 seconds, is there a way to speed it up somehow?
any tweaks and tuneups possible with it?

thanks!
----------------
eugene

  2. Re: POSTGRES DB 3 800 000 rows table, speed up?

    Jim C. Nasby <jnasby@pervasive.com> — 2005-12-28T17:33:02Z

    On Tue, Dec 27, 2005 at 11:25:37PM +0200, Eugene wrote:
> I've indexed first two columns they are IPfrom, IPto also table is btree
> version of postgre is 7.4.8, on hosting

You should ask them to upgrade; 7.4 is getting pretty old.

> I ask db like this  SELECT * FROM ipdb2 WHERE '3229285376' BETWEEN ipfrom  
> AND ipto;

I'm pretty sure PostgreSQL won't be able to use any indexes for this
(EXPLAIN ANALYZE would verify that). Instead, expand the between out:

WHERE ipfrom >= '...' AND ipto <= '...'

Also, generally speaking, databases and CamelCase don't mix too well;
you'll probably be happier doing something like ip_from and ip_to.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

  3. Re: POSTGRES DB 3 800 000 rows table, speed up?

    Jonel Rienton <jonel@rientongroup.com> — 2005-12-28T18:27:34Z

    I have a question about this, shouldn't it be the query should look like

SELECT * 
FROM ipdb2
WHERE 3229285376 BETWEEN ipfrom AND ipto


Note the query doesn't quote the filter, since the ipfrom and ipto are both
integer types? 

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jim C. Nasby
Sent: Wednesday, December 28, 2005 11:33 AM
To: Eugene
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] POSTGRES DB 3 800 000 rows table, speed up?

On Tue, Dec 27, 2005 at 11:25:37PM +0200, Eugene wrote:
> I've indexed first two columns they are IPfrom, IPto also table is 
> btree version of postgre is 7.4.8, on hosting

You should ask them to upgrade; 7.4 is getting pretty old.

> I ask db like this  SELECT * FROM ipdb2 WHERE '3229285376' BETWEEN 
> ipfrom AND ipto;

I'm pretty sure PostgreSQL won't be able to use any indexes for this
(EXPLAIN ANALYZE would verify that). Instead, expand the between out:

WHERE ipfrom >= '...' AND ipto <= '...'

Also, generally speaking, databases and CamelCase don't mix too well; you'll
probably be happier doing something like ip_from and ip_to.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

  4. Re: POSTGRES DB 3 800 000 rows table, speed up?

    hubert depesz lubaczewski <depesz@gmail.com> — 2005-12-28T18:50:17Z

    On 12/27/05, Eugene <evgenius@hot.ee> wrote:
>
> Hello!
> I've managed to import into postgre DB 3 800 000 rows of data (500 MB pure
> CSV ~ 2 GB SQL DB)
> It looks like this
> "69110784","69111807","US","UNITED
> STATES","ILLINOIS","BLOOMINGTON","40.4758","-88.9894","61701","LEVEL 3
> COMMUNICATIONS INC","DSL-VERIZON.NET"
> "69111808","69112831","US","UNITED
> STATES","TEXAS","GRAPEVINE","32.9309","-97.0755","76051","LEVEL 3
> COMMUNICATIONS INC","DSL-VERIZON.NET"
> "69112832","69113087","US","UNITED
> STATES","TEXAS","DENTON","33.2108","-97.1231","76201","LEVEL 3
> COMMUNICATIONS INC","DSL-VERIZON.NET"
> CREATE TABLE ipdb2
> (
>      ipFROM int4 NOT NULL,
>      ipTO int4 NOT NULL ,
>     countrySHORT CHARACTER(2) NOT NULL,
>       countryLONG VARCHAR(64) NOT NULL,
>      ipREGION VARCHAR(128) NOT NULL,
>      ipCITY VARCHAR(128) NOT NULL,
>    ipLATITUDE  DOUBLE PRECISION,
>      ipLONGITUDE  DOUBLE PRECISION,
>      ipZIPCODE VARCHAR(5),
>      ipISP VARCHAR(255) NOT NULL,
>      ipDOMAIN VARCHAR(128) NOT NULL
> );
>

1st. of all - change ipfrom and ipto column types to int8.
integer types in postgresql are signed, so their effective "max" is around
2000000000, which makes your example with over 3000000000 technically not
working.
2nd. do vacuum analyze
3rd. show explain analyze. how can we tell you how to speed it up, when we
dont know what/how postgres is doing with it.

depesz

  5. Re: POSTGRES DB 3 800 000 rows table, speed up?

    Tom Lane <tgl@sss.pgh.pa.us> — 2005-12-28T19:04:28Z

    "Jim C. Nasby" <jnasby@pervasive.com> writes:
> On Tue, Dec 27, 2005 at 11:25:37PM +0200, Eugene wrote:
>> I ask db like this  SELECT * FROM ipdb2 WHERE '3229285376' BETWEEN ipfrom  
>> AND ipto;

> I'm pretty sure PostgreSQL won't be able to use any indexes for this
> (EXPLAIN ANALYZE would verify that). Instead, expand the between out:

> WHERE ipfrom >= '...' AND ipto <= '...'

That won't help (it is in fact exactly the same query, because BETWEEN
is just rewritten into that).  The real problem is that btree indexes
are ill-suited to this type of condition.  If the typical row has only
a small distance between ipfrom and ipto then the query is actually
pretty selective, but there is no way to capture that selectivity in
a btree search, because neither of the single-column comparisons are
selective at all.  The planner realizes this and doesn't bother with
the index, instead it just does a seqscan.

You could probably get somewhere by casting the problem as an rtree
or GIST overlap/containment query, but with the currently available
tools it would be a pretty unnatural-looking query ... probably
something like
	box(point(ipfrom,ipfrom),point(ipto,ipto)) ~
	box(point(3229285376,3229285376),point(3229285376,3229285376))
after creating an rtree or GIST index on
	box(point(ipfrom,ipfrom),point(ipto,ipto))
(haven't tried this but there is a solution lurking somewhere in this
general vicinity).

Is there a good reason why the data is stored this way, and not as
say a single "cidr" column containing subnet addresses?  Querying
	WHERE '192.122.252.0' << cidrcolumn
would be a much more transparent way of expressing your problem.
We don't currently have an easy indexing solution for that one either,
but we might in the future.

			regards, tom lane

  6. Re: POSTGRES DB 3 800 000 rows table, speed up?

    SunWuKung <balazs.klein@axelero.hu> — 2005-12-29T02:24:36Z

    Could you explain this a little bit more?
What are the conditions of this situation that makes b-tree ineffective?

Thanks
SWK

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: 2005. december 28. 20:04
To: Jim C. Nasby
Cc: Eugene; pgsql-general@postgresql.org
Subject: Re: [GENERAL] POSTGRES DB 3 800 000 rows table, speed up? 

"Jim C. Nasby" <jnasby@pervasive.com> writes:
> On Tue, Dec 27, 2005 at 11:25:37PM +0200, Eugene wrote:
>> I ask db like this  SELECT * FROM ipdb2 WHERE '3229285376' BETWEEN ipfrom

>> AND ipto;

> I'm pretty sure PostgreSQL won't be able to use any indexes for this
> (EXPLAIN ANALYZE would verify that). Instead, expand the between out:

> WHERE ipfrom >= '...' AND ipto <= '...'

That won't help (it is in fact exactly the same query, because BETWEEN
is just rewritten into that).  The real problem is that btree indexes
are ill-suited to this type of condition.  If the typical row has only
a small distance between ipfrom and ipto then the query is actually
pretty selective, but there is no way to capture that selectivity in
a btree search, because neither of the single-column comparisons are
selective at all.  The planner realizes this and doesn't bother with
the index, instead it just does a seqscan.

You could probably get somewhere by casting the problem as an rtree
or GIST overlap/containment query, but with the currently available
tools it would be a pretty unnatural-looking query ... probably
something like
	box(point(ipfrom,ipfrom),point(ipto,ipto)) ~
	box(point(3229285376,3229285376),point(3229285376,3229285376))
after creating an rtree or GIST index on
	box(point(ipfrom,ipfrom),point(ipto,ipto))
(haven't tried this but there is a solution lurking somewhere in this
general vicinity).

Is there a good reason why the data is stored this way, and not as
say a single "cidr" column containing subnet addresses?  Querying
	WHERE '192.122.252.0' << cidrcolumn
would be a much more transparent way of expressing your problem.
We don't currently have an easy indexing solution for that one either,
but we might in the future.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly

  7. Re: POSTGRES DB 3 800 000 rows table, speed up?

    Tom Lane <tgl@sss.pgh.pa.us> — 2005-12-29T03:21:44Z

    =?iso-8859-1?Q?Klein_Bal=E1zs?= <Balazs.Klein@axelero.hu> writes:
> Could you explain this a little bit more?
> What are the conditions of this situation that makes b-tree ineffective?

Well, what he's trying to do is (abstracting a little)

    WHERE low_bound_col <= probe_value AND probe_value <= high_bound_col

Given a btree index on low_bound_col, the best you could do with this is
scan all the index entries from the start of the index up to probe_value
... or about half the table, on average, which makes the index pretty
much useless.  On the assumption that low_bound_col and high_bound_col
are usually close together, all of the useful hits will occur near the
end of that scan, or the beginning if you scan backwards --- but there's
no way to know when it's OK to stop looking.

Making a double-column index on (low_bound_col, high_bound_col) does
not improve the situation much, because the additional condition
high_bound_col >= probe_value doesn't let you avoid scanning small
values of low_bound_col.  You might save some trips to the table proper
but you're still scanning half the index.

And of course indexing (high_bound_col, low_bound_col) isn't any better.

If you are willing to impose a hard-wired assumption about the possible
size of the low-bound-to-high-bound distance, you can extend the query
to something like

    WHERE low_bound_col <= probe_value AND probe_value <= high_bound_col
	AND low_bound_col >= (probe_value - max_distance)

which creates an efficiently indexable range limitation on
low_bound_col.  Of course this is a very sucky kluge.

You can do a lot better with index types that are designed for
two-dimensional data instead of one-dimensional data.  Btree is
a great data structure for one-dimensional searches, but that
doesn't make it the answer to everything.

			regards, tom lane

  8. Re: POSTGRES DB 3 800 000 rows table, speed up?

    Manfred Koizar <mkoi-pg@aon.at> — 2006-01-03T14:38:03Z

    On Tue, 27 Dec 2005 23:25:37 +0200, Eugene <evgenius@hot.ee> wrote:
>SELECT * FROM ipdb2 WHERE '3229285376' BETWEEN ipfrom AND ipto;

If your [ipfrom, ipto] ranges are non-overlapping, you might be able
to exploit that fact by adding something like

	... ORDER BY ipfrom DESC LIMIT 1

Servus
 Manfred

  9. Re: POSTGRES DB 3 800 000 rows table, speed up?

    Andrew - Supernews <andrew+nonews@supernews.com> — 2006-01-03T14:59:26Z

    On 2005-12-27, Eugene <evgenius@hot.ee> wrote:
> I've indexed first two columns they are IPfrom, IPto also table is btree
> version of postgre is 7.4.8, on hosting
> I ask db like this  SELECT * FROM ipdb2 WHERE '3229285376' BETWEEN ipfrom  
> AND ipto;
>
> and get answer after 3-10 seconds, is there a way to speed it up somehow?
> any tweaks and tuneups possible with it?

http://pgfoundry.org/projects/ip4r

Designed specifically for storing IP ranges as in your example, it will
(if correctly used) reduce that query time down to a few milliseconds.

-- 
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services