pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron Johnson <ron.l.johnson@cox.net>
To: PgSQL Performance ML <pgsql-performance@postgresql.org>
Subject: Re: Slow query on OS X box
Date: 22 Jan 2003 14:35:22 -0600
Message-ID: <1043267721.22135.141.camel@haggis> (raw)
In-Reply-To: <OF5B777E26.43FD52D5-ON88256CB6.00635521@fds.com>
References: <OF5B777E26.43FD52D5-ON88256CB6.00635521@fds.com>

What about creating a multi-segment index on wizard/sdate?

On a side note: that record is ~8KB long, which is kinda big.  You could
split those column into a seperate table (or tables), so that when you
want to query, say, gender, department & trip_length, you won't have to
read in *so*much* extra data, slowing the query down.

Also, these column sizes seem kind excessive, and allow for bad data to 
seep in to the table:
  timestamp varchar(75),
  age varchar(25),
  metal varchar(255),
  gem varchar(255),
  bra_size varchar(25),

On Wed, 2003-01-22 at 12:26, Patrick Hatcher wrote:
> I have a table that contains over 13 million rows.  This query takes an
> extremely long time to return.  I've vacuum full, analyzed, and re-indexed
> the table. Still the results are the same.  Any ideas?
> TIA
> Patrick
> 
> mdc_oz=# explain analyze select wizard from search_log where wizard
> ='Keyword' and sdate between '2002-12-01' and '2003-01-15';
>                                                          QUERY PLAN
> -----------------------------------------------------------------------------
>  Seq Scan on search_log  (cost=0.00..609015.34 rows=3305729 width=10)
> (actual time=99833.83..162951.25 rows=3280573 loops=1)
>    Filter: ((wizard = 'Keyword'::character varying) AND (sdate >
> = '2002-12-01'::date) AND (sdate <= '2003-01-15'::date))
>  Total runtime: 174713.25 msec
> (3 rows)
> 
> My box I'm running PG on:
> Dual 500 Mac OS X
> 1g  ram
> Pg 7.3.0
> 
> Conf settings
> max_connections = 200
> shared_buffers = 15200
> #max_fsm_relations = 100        # min 10, fsm is free space map, ~40 bytes
> #max_fsm_pages = 10000          # min 1000, fsm is free space map, ~6 bytes
> #max_locks_per_transaction = 64 # min 10
> #wal_buffers = 8                # min 4, typically 8KB each
> 
> 
> 
> 
> CREATE TABLE public.search_log (
>   wizard varchar(50) NOT NULL,
>   sub_wizard varchar(50),
>   timestamp varchar(75),
>   department int4,
>   gender varchar(25),
>   occasion varchar(50),
>   age varchar(25),
>   product_type varchar(2000),
>   price_range varchar(1000),
>   brand varchar(2000),
>   keyword varchar(1000),
>   result_count int4,
>   html_count int4,
>   fragrance_type varchar(50),
>   frag_type varchar(50),
>   frag_gender char(1),
>   trip_length varchar(25),
>   carry_on varchar(25),
>   suiter varchar(25),
>   expandable varchar(25),
>   wheels varchar(25),
>   style varchar(1000),
>   heel_type varchar(25),
>   option varchar(50),
>   metal varchar(255),
>   gem varchar(255),
>   bra_size varchar(25),
>   feature1 varchar(50),
>   feature2 varchar(50),
>   feature3 varchar(50),
>   sdate date,
>   stimestamp timestamptz,
>   file_name text
> ) WITH OIDS;
> 
> CREATE INDEX date_idx ON search_log USING btree (sdate);
> CREATE INDEX slog_wizard_idx ON search_log USING btree (wizard);
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
-- 
+---------------------------------------------------------------+
| Ron Johnson, Jr.        mailto:ron.l.johnson@cox.net          |
| Jefferson, LA  USA      http://members.cox.net/ron.l.johnson  |
|                                                               |
| "My advice to you is to get married: If you find a good wife, |
| you will be happy; if not, you will become a philosopher."    |
|    Socrates                                                   |
+---------------------------------------------------------------+




view thread (8+ messages)  latest in thread

Message-ID: <1043267721.22135.141.camel@haggis>
Permalink:  ../1043267721.22135.141.camel@haggis/
Also on:    postgresql.org/message-id/1043267721.22135.141.camel@haggis

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: ron.l.johnson@cox.net
  Subject: Re: Slow query on OS X box
  In-Reply-To: <1043267721.22135.141.camel@haggis>
  References: <OF5B777E26.43FD52D5-ON88256CB6.00635521@fds.com> <1043267721.22135.141.camel@haggis>

* 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