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

Re: tuning seqscan costs

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: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackerspgsql-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 = + 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
>                            = + 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


If offset is a column in offsets, can you add an index on the  
expresion + offset?

Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Open Source Solutions. Optimized Web Development.
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5151 (fax)

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2005-10-26 23:06:15
Subject: Re: Materializing a sequential scan
Previous:From: PFCDate: 2005-10-26 21:49:54
Subject: Re: browsing table with 2 million records

pgsql-hackers by date

Next:From: Jim C. NasbyDate: 2005-10-26 23:06:19
Subject: Re: TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)", File: "nbtsearch.c", Line: 89)
Previous:From: Tom LaneDate: 2005-10-26 22:38:45
Subject: Re: TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)", File: "nbtsearch.c", Line: 89)

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