SEO

#1 (**permalink**) 04-18-2008, 12:20 PM

> Table "public.address"
> Column | Type | Modifiers
> ----------------------+------------------------+-----------
> postcode_top | character varying(2) | not null
> postcode_middle | character varying(4) | not null
> postcode_bottom | character varying(7) | not null

consider making above fields char(x) not varchar(x) for small but
important savings.

> postcode | character varying(10) | not null
> property_type | character varying(15) | not null
> sale_type | character varying(10) | not null
> flat_extra | character varying(100) | not null
> number | character varying(100) | not null
> street | character varying(100) | not null
> locality_1 | character varying(100) | not null
> locality_2 | character varying(100) | not null
> city | character varying(100) | not null
> county | character varying(100) | not null
> Indexes:
> "address_city_index" btree (city)
> "address_county_index" btree (county)
> "address_locality_1_index" btree (locality_1)
> "address_locality_2_index" btree (locality_2)
> "address_pc_bottom_index" btree (postcode_bottom)
> "address_pc_middle_index" btree (postcode_middle)
> "address_pc_top_index" btree (postcode_top)
> "address_pc_top_middle_bottom_index" btree (postcode_top,
> postcode_middle, postcode_bottom)
> "address_pc_top_middle_index" btree (postcode_top,
postcode_middle)
> "address_postcode_index" btree (postcode)
> "address_property_type_index" btree (property_type)
> "address_street_index" btree (street)
> "street_prefix" btree (lower("substring"((street)::text, 1, 1)))
>
> Obviously, to me, this is a problem, I need these queries to be under
a
> second to complete. Is this unreasonable? What can I do to make this
"go
> faster"? I've considered normalising the table but I can't work out
> whether the slowness is in dereferencing the pointers from the index
> into the table or in scanning the index in the first place. And
> normalising the table is going to cause much pain when inserting
values
> and I'm not entirely sure if I see why normalising it should cause a
> massive performance improvement.

http://www.dbdebunk.com

> I need to get to the stage where I can run queries such as:
> 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
>
> and have the results very quickly.
>
> Any help most gratefully received (even if it's to say that I should
be
> posting to a different mailing list!).

this is correct list. did you run vacuum/analyze, etc?
Please post vacuum analyze times.

Merlin

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

#2 (**permalink**) 04-18-2008, 12:20 PM

On Thu, Sep 01, 2005 at 02:04:54PM -0400, Merlin Moncure wrote:
> > Any help most gratefully received (even if it's to say that I should
> be
> > posting to a different mailing list!).
>
> this is correct list. did you run vacuum/analyze, etc?
> Please post vacuum analyze times.

2005-09-01 19:47:08 LOG: statement: vacuum full analyze address;
2005-09-01 19:48:44 LOG: duration: 96182.777 ms

2005-09-01 19:50:20 LOG: statement: vacuum analyze address;
2005-09-01 19:51:48 LOG: duration: 87675.268 ms

I run them regularly, pretty much after every bulk import.

Matthew

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

#3 (**permalink**) 04-18-2008, 12:21 PM

On Thu, Sep 01, 2005 at 02:04:54PM -0400, Merlin Moncure wrote:
> > Table "public.address"
> > Column | Type | Modifiers
> > ----------------------+------------------------+-----------
> > postcode_top | character varying(2) | not null
> > postcode_middle | character varying(4) | not null
> > postcode_bottom | character varying(7) | not null
>
> consider making above fields char(x) not varchar(x) for small but
> important savings.

Huh, hang on -- AFAIK there's no saving at all by doing that. Quite the
opposite really, because with char(x) you store the padding blanks,
which are omitted with varchar(x), so less I/O (not necessarily a
measurable amount, mind you, maybe even zero because of padding issues.)

--
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
You liked Linux a lot when he was just the gawky kid from down the block
mowing your lawn or shoveling the snow. But now that he wants to date
your daughter, you're not so sure he measures up. (Larry Greenemeier)

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Re: Massive performance issues