1

I have a PostgreSQL database containing pages and links downloaded by a web crawler, with the following tables:

pages
----------
id:          Integer (primary key)
url:         String  (unique)
title:       String
text:        String
html:        String
last_visit:  DateTime
word_pos:    TSVECTOR


links
----------
id         Integer (primary key)
source:    String
target:    String  
link_text: String
UNIQUE(source,target)


crawls
---------
id:         Integer (primary key)
query:      String


crawl_results
-------------
id:       Integer (primary key)
score:    Integer (constraint 0<=score<=1)
crawl_id: Integer (foreign key, crawls.id)
page_id:  Integer (foreign key, pages.id)

The source and target fields in the links table contain URLs. I am running the following query to extract scored links from the top-ranking search results, for pages that haven't been fetched yet:

WITH top_results AS 
    (SELECT page_id, score FROM crawl_results 
     WHERE crawl_id=$1 
     ORDER BY score LIMIT 100)
SELECT top_results.score, l.target
FROM top_results 
    JOIN pages p ON top_results.page_id=p.id
    JOIN links l on p.url=l.source 
WHERE NOT EXISTS (SELECT pp.id FROM pages pp WHERE l.target=pp.url)

However, I would like to filter these results so that only one row is returned for a given domain (the one with the lowest score). So for instance, if I get (0.3, 'http://www.foo.com/bar') and (0.8, 'http://www.foo.com/zor'), I only want the first because it has same domain foo.com and has the lower score.

I was able to find documentation for the builtin full text search parsers, which can parse URLS and extract the hostname. For instance, I can extract the hostname from a URL as follows:

SELECT token FROM ts_parse('default', 'http://www.foo.com') WHERE tokid = 6;

    token    
-------------
 www.foo.com
(1 row)

However, I can't figure out how I would integrate this into the above query to filter out duplicate domains from the results. And because this is the docs for "testing and debugging text search", I don't know if this use of ts_parse() is even related to how the URL parser is intended to be used in practice.

How would I use the host parser in my query above to return one row per domain? Also, how would I appropriately index the links table for host and url lookup?

3
  • The whole point of full-text search indexes in PostgreSQL is to be able to do this kind of parsing upfront on INSERT, and maintain an index for quick lookup of tokens (in this case host field). I feel like there is probably a better way using builtin URL parser, and FTS index, instead of creating a separate table for hostnames and JOINing. One of the benefits would be that I wouldn't have to do two INSERTs for every URL (one for URL, one for hosts table).
    – J. Taylor
    Apr 6, 2019 at 17:50
  • I want to limit results to those per host for this particular query. There are other queries where I will need to extract scheme and url_path in addition to host. I understand that there are a variety of ways to solve this problem, including using postgres regex functions to extract the info, or generating extra tables to contain the info (currently I'm using Python to parse). But I'm most interested right now in learning how to use the builtin postgres parsers for extracting this info from URL fields, and the docs are quite sparse on how to use the URL parser in practice.
    – J. Taylor
    Apr 8, 2019 at 1:14
  • ... not only are the official docs very sparse for how to use the builtin URL parser, but there is hardly any info I can find online (for instance, not a single question on this site that talks about how to use it). I feel that an answer here would also be helpful for others who are searching for details on how to use them. Again, I understand that there are many other ways to solve this problem, but I specifically am looking for information on how to use the Postgres URL parser, rather than how to implement one myself.
    – J. Taylor
    Apr 8, 2019 at 1:19

1 Answer 1

1

After asking around on IRC, the Postgres mailing list, and a few other places, this is the best I've been able to come up with. I'm going to put it up in case it helps someone else with similar problem,and also because I'm hoping that someone can tell me how to improve on it. I first created the following views:

    CREATE VIEW scored_pages AS (
        SELECT crawl_results.crawl_id, crawl_results.score, 
            crawl_results.page_id, pages.url
        FROM crawl_results 
        JOIN pages ON crawl_results.page_id = pages.id 
    );


    CREATE VIEW scored_links AS (
        SELECT scored_pages.score, links.source, 
            links.target, links.link_text
        FROM links
        JOIN scored_pages ON scored_pages.url = links.source
    );

Then, using these views, I did the following query to extract the links from the lowest scored pages in the results:

SELECT score, host, target 
FROM (
        SELECT DISTINCT ON (token) token AS host, score, target 
        FROM scored_links,
        LATERAL ts_parse('default', target) WHERE tokid = 6
        ORDER BY token, score
) as x
WHERE NOT EXISTS (SELECT pp.id FROM pages pp WHERE target=pp.url)
ORDER BY score;

I would appreciate any feedback on how to improve this, or anything I'm doing wrong. In the meantime, I'll leave this question open in case someone comes by later with a better answer.

1
  • Worked for me, thanks for sharing! I cannot believe postgres doesn't have a more straightforward way of parsing URLs. Even Presto has a simple function for it.
    – autonopy
    Jul 13, 2022 at 19:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.