Re: Query performance question on a large table

Hi Tom,
I've upgraded to 7.4.1. It seems to be working fine - haven't encountered
any problems yet.
The upgrade didn't lead to the desired outcome however. The query doesn't
run faster then under v7.3.2.

I have the following relations:
tgr=# \d t_fome -- 46 rows
Table "public.t_fome"
Column | Type | Modifiers
-----------+--------------------------+-----------
fomeazon | integer | not null
fomenev | character varying(50) |
inuse | character(4) |
mecsazon | integer |
merotipus | character(10) |
szbevont | character(1) |
utmodido | timestamp with time zone |
visible | character(1) |
Indexes:
"t_fome_pkey" primary key, btree (fomeazon)
"idx_t_fome_fomeazon" btree (fomeazon)
"idx_t_fome_inuse" btree (inuse)
"idx_t_fome_lower_inuse" btree (lower((inuse)::text))
"idx_t_fome_mecsazon" btree (mecsazon)

tgr=# \d t_me30 -- 4518927 rows
Table "public.t_me30"
Column | Type | Modifiers
--------------+--------------------------+-----------
fomeazon | integer |
mertido | character(16) |
ertektipus | character(10) |
hetnap | character(1) |
impulzusszam | double precision |
mertertek | double precision |
merttartam | integer |
utmodido | timestamp with time zone |
Indexes:
"idx_t_me30_ertektipus" btree (ertektipus)
"idx_t_me30_fomeazon" btree (fomeazon)
"idx_t_me30_mertido" btree (mertido)
"idx_t_me30_mertido_fomeazon_ertektipus" btree (mertido, fomeazon,
ertektipus)
"idx_t_me30_utmodido" btree (utmodido)

I found that:

1. explain select fomeazon from t_fome where lower(inuse) = 'igen'
QUERY PLAN
------------------------------------------------------
Seq Scan on t_fome (cost=0.00..1.80 rows=1 width=4)
Filter: (lower((inuse)::text) = 'igen'::text)

As the table has an index on lower((inuse)::text), I belive it should be
used for searching.

2. explain select mertido, fomeazon, ertektipus, mertertek
from t_me30 where fomeazon in (select fomeazon from t_fome where
lower(inuse) = 'igen') and
mertido like '2003-12-17%' and ertektipus in ('+MW') order by mertido,
fomeazon, ertektipus;
QUERY PLAN
----------------------------------------------------------------------------
---------------
Sort (cost=128045.87..128045.93 rows=24 width=46)
Sort Key: t_me30.mertido, t_me30.fomeazon, t_me30.ertektipus
-> Hash IN Join (cost=1.81..128045.32 rows=24 width=46)
Hash Cond: ("outer".fomeazon = "inner".fomeazon)
-> Seq Scan on t_me30 (cost=0.00..128037.62 rows=1129 width=46)
Filter: ((mertido ~~ '2003-12-17%'::text) AND (ertektipus =
'+MW'::bpchar))
-> Hash (cost=1.80..1.80 rows=1 width=4)
-> Seq Scan on t_fome (cost=0.00..1.80 rows=1 width=4)
Filter: (lower((inuse)::text) = 'igen'::text)

In the first line of query plan we have a sort operation which is the most
expensive part of the plan. Having an index on (mertido, fomeazon,
ertektipus) key, shouldn't it be used to sort the result set? Like doesn't
use the index (mertido) either.

How could I make Postgres to use these indexes. Is there any other way to
make lower the costs on sort operations and as a result the query run time?

Thank you all,

-- Csaba Együd

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Tom Lane
> Sent: 2004. január 6. 21:04
> To: csegyud(at)vnet(dot)hu
> Cc: Pgsql-General(at)Postgresql(dot)Org (E-mail)
> Subject: Re: [GENERAL] Query performance question on a large table
>
>
> =?iso-8859-2?Q?Egy=FCd_Csaba?= <csegyud(at)vnet(dot)hu> writes:
> > here is a sample query:
> > select mertido, fomeazon, ertektipus, mertertek from
> t_me30 where fomeazon
> > in (select distinct fomeazon from t_fome where lower(inuse)
> = 'igen') and
> > mertido like '2003-12-17%' and ertektipus in ('+MW') order
> by mertido,
> > fomeazon, ertektipus;
>
> > Ohh, I nearly forgot the config: Linux 7.1; Postgres 7.3.2;
>
> The first thing you ought to do is move to PG 7.4. "foo IN
> (SELECT ...)"
> generally works a lot better under 7.4 than prior releases.
> I'd suggest
> dropping the "DISTINCT" when using 7.4, too.
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo(at)postgresql(dot)org
>
>
>
> -- Incoming mail is certified Virus Free.
> Checked by AVG Anti-Virus (http://www.grisoft.com)
> Version: 7.0.209 / Virus Database: 261 - Release Date: 2004. 01. 02.
>