pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Joshua D. Drake <jd@commandprompt.com>
To: Alex Turner <armtuk@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Planner issue
Date: Tue, 22 Mar 2005 08:22:59 -0800
Message-ID: <42404663.2010504@commandprompt.com> (raw)
In-Reply-To: <33c6269f05032206565ca4f9f1@mail.gmail.com>
References: <33c6269f05032206565ca4f9f1@mail.gmail.com>

Alex Turner wrote:

>I get the following output from explain analyze on a certain subset of
>a large query I'm doing.
>
>  
>
Try increases the statistics on the listprice column with alter
table and then re-run analyze.

alter table foo alter column set statistics <n>

Sincerely,

Joshua D. Drake


>>From the looks of it, I need to increase how often postgres uses an
>index over a seq scan, but I'm not sure how to do that.  I looked
>through the run-time configuration docs on the website, but didn't see
>anything pertaining to index selectivity.
>
>Thanks,
>
>Alex Turner
>netEconomist
>
>
>trendmls=# explain analyze select listnum from propmain where
>listprice<=300000 and listprice>=220000;
>                                                  QUERY PLAN                    
>--------------------------------------------------------------------------------------------------------------
> Seq Scan on propmain  (cost=0.00..15556.05 rows=6228 width=4) (actual
>time=0.093..506.730 rows=5671 loops=1)
>   Filter: ((listprice <= 300000::numeric) AND (listprice >= 220000::numeric))
> Total runtime: 510.482 ms
>(3 rows)
>
>trendmls=# explain analyze select listnum from propmain where
>listprice<=300000 and listprice>=250000;
>                                                                QUERY PLAN      
>------------------------------------------------------------------------------------------------------------------------------------------
> Index Scan using propmain_listprice_i on propmain 
>(cost=0.00..12578.65 rows=3486 width=4) (actual time=0.103..16.418
>rows=3440 loops=1)
>   Index Cond: ((listprice <= 300000::numeric) AND (listprice >=
>250000::numeric))
> Total runtime: 18.528 ms
>(3 rows)
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>  
>


-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

Attachments:

jd.vcf (text/x-vcard, 283B) download begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:jd@commandprompt.com title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard

view thread (6+ messages)  latest in thread

Message-ID: <42404663.2010504@commandprompt.com>
Permalink:  ../42404663.2010504@commandprompt.com/
Also on:    postgresql.org/message-id/42404663.2010504@commandprompt.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: jd@commandprompt.com, armtuk@gmail.com
  Subject: Re: Planner issue
  In-Reply-To: <42404663.2010504@commandprompt.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox