From: | "Thomas F(dot) O'Connell" <tfo(at)sitening(dot)com> |
---|---|
To: | Katherine Stoovs <ambrosiac(at)nedsenta(dot)nl> |
Cc: | PgSQL Performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: tuning seqscan costs |
Date: | 2005-10-26 22:48:17 |
Message-ID: | 48454B5A-D7D5-4387-B00F-CF5D1B9916EC@sitening.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-performance |
On Oct 19, 2005, at 9:51 AM, Katherine Stoovs wrote:
> I want to correlate two index rows of different tables to find an
> offset so that
>
> table1.value = table2.value AND table1.id = table2.id + offset
>
> is true for a maximum number of rows.
>
> To achieve this, I have the two tables and a table with possible
> offset values and execute a query:
>
> SELECT value,(SELECT COUNT(*) FROM table1,table2
> WHERE table1.value = table2.value AND
> table1.id = table2.id + offset)
> AS matches FROM offsets ORDER BY matches;
>
> The query is very inefficient, however, because the planner doesn't
> use my indexes and executes seqscans instead. I can get it to execute
> fast by setting ENABLE_SEQSCAN to OFF, but I have read this will make
> the performance bad on other query types so I want to know how to
> tweak the planner costs or possibly other stats so the planner will
> plan the query correctly and use index scans. There must be something
> wrong in the planning parameters after all if a plan that is slower by
> a factor of tens or hundreds becomes estimated better than the fast
> variant.
>
> I have already issued ANALYZE commands on the tables.
>
> Thanks for your help,
> Katherine Stoovs
Katherine,
If offset is a column in offsets, can you add an index on the
expresion table2.id + offset?
http://www.postgresql.org/docs/8.0/static/indexes-expressional.html
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Open Source Solutions. Optimized Web Development.
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)
From | Date | Subject | |
---|---|---|---|
Next Message | Jim C. Nasby | 2005-10-26 23:06:19 | Re: TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)", File: "nbtsearch.c", Line: 89) |
Previous Message | Tom Lane | 2005-10-26 22:38:45 | Re: TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)", File: "nbtsearch.c", Line: 89) |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-10-26 23:06:15 | Re: Materializing a sequential scan |
Previous Message | PFC | 2005-10-26 21:49:54 | Re: browsing table with 2 million records |