From: Mark Kirkwood <markir@paradise.net.nz>
To: Matthew Sackman <matthew@lshift.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Massive performance issues
Date: Fri, 02 Sep 2005 15:19:53 +1200
Message-ID: <4317C4D9.9070106@paradise.net.nz> (raw)
In-Reply-To: <20050901174231.GE23970@pongo.lshift.net>
References: <20050901174231.GE23970@pongo.lshift.net>
It would be good to see EXPLAIN ANALYZE output for the three queries
below (the real vs. estimated row counts being of interest).
The number of pages in your address table might be interesting to know too.
regards
Mark
Matthew Sackman wrote (with a fair bit of snippage):
> explain select locality_2 from address where locality_2 = 'Manchester';
> gives
> QUERY PLAN
> ----------------------------------------------------------------
> Seq Scan on address (cost=0.00..80677.16 rows=27923 width=12)
> Filter: ((locality_2)::text = 'Manchester'::text)
>
>
> explain select locality_1 from address where locality_1 = 'Manchester';
> gives
> QUERY PLAN
> ----------------------------------------------------------------
> Index Scan using address_locality_1_index on address
> (cost=0.00..69882.18 rows=17708 width=13)
> Index Cond: ((locality_1)::text = 'Manchester'::text)
>
>
> select street, locality_1, locality_2, city from address
> where (city = 'Nottingham' or locality_2 = 'Nottingham'
> or locality_1 = 'Nottingham')
> and upper(substring(street from 1 for 1)) = 'A'
> group by street, locality_1, locality_2, city
> order by street
> limit 20 offset 0
>
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: markir@paradise.net.nz, matthew@lshift.net
Subject: Re: Massive performance issues
In-Reply-To: <4317C4D9.9070106@paradise.net.nz>
* 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