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

Re: efficient storing of urls

From: Sean Shanny <shannyconsulting(at)earthlink(dot)net>
To: Shane Wegner <shane-keyword-pgsql(dot)a1e0d9(at)cm(dot)nu>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: efficient storing of urls
Date: 2004-02-27 23:00:36
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general

Can you give an example of a query that has gotten slower due to the 
increasing size of the urls table with an explain analyze?



Shane Wegner wrote:

>Hello list,
>I have a database where one of the tables stores urls and
>it's getting to the point where queries are getting quite
>slow.  My urls table looks something like:
>create table urls(
>id serial,
>url text,
>primary key(id)
>What I am thinking of doing is storing urls in a tree-like
>create table urls(
>id serial,
>url_part text,
>parent_id int, -- references back to urls table
>insert into urls (id,parent_id,url_part) (1, NULL,
>insert into url (id,parent_id,url_part) values(2, 1, '/images');
>url id 2 would represent without
>actually storing the full hostname and path for each url.
>Is this a recommended way of storing urls or is there a
>better way?  Is it likely to result in faster joins as each
>row will be smaller?
>One final question, how would one get the full url back out
>of the sql table referencing the parent back to the root
>(null parent) for use by an sql like query and would that
>procedure negate any performance benefits by this storage
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend

In response to


pgsql-general by date

Next:From: Bill MoranDate: 2004-02-27 23:16:28
Subject: Re: efficient storing of urls
Previous:From: Shane WegnerDate: 2004-02-27 22:53:54
Subject: efficient storing of urls

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