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

Re: Planner should use index on a LIKE 'foo%' query

From: Moritz Onken <onken(at)houseofdesign(dot)de>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Planner should use index on a LIKE 'foo%' query
Date: 2008-06-30 12:56:57
Message-ID: 2EAD1377-002D-4155-B9FB-6D52A46281B7@houseofdesign.de (view raw or flat)
Thread:
Lists: pgsql-performance
>
>
> However there's a lot more scope for improving a query along these  
> lines, like adding indexes, or CLUSTERing on an index. It depends  
> what other queries you are wanting to run.
>
> I don't know how much update/insert activity there will be on your  
> database. However, if you were to add an index on the URL on both  
> tables, then CLUSTER both tables on those indexes, and ANALYSE, then  
> this query should run as a merge join, and be pretty quick.
>
> However, this is always going to be a long-running query, because it  
> accesses at least one whole table scan of a large table.
>
> Matthew

There are already indexes on the url columns. I didn't cluster yet but  
this is a pretty good idea, thanks. There will be no updates or  
inserts. It's static data for research purposes.

moritz

In response to

pgsql-performance by date

Next:From: Marko KreenDate: 2008-06-30 13:16:26
Subject: Re: Federated Postgresql architecture ?
Previous:From: Matthew WakelingDate: 2008-06-30 12:52:08
Subject: Re: Planner should use index on a LIKE 'foo%' query

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