Re: Query performance question on a large table

Sorry, I just have found the thread concerning about index useage in
connection with like clause.
I created an index on mertido using
CREATE INDEX idx_t_me30_mertido2 ON t_me30 USING btree (mertido
bpchar_pattern_ops);
command and analyzed and the query plan now is:

tgr=# 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 i
n ('+MW') order by mertido, fomeazon, ertektipus;
QUERY PLAN
----------------------------------------------------------------------------
----------------------------
Sort (cost=7.84..7.85 rows=1 width=46)
Sort Key: t_me30.mertido, t_me30.fomeazon, t_me30.ertektipus
-> Nested Loop IN Join (cost=0.00..7.83 rows=1 width=46)
Join Filter: ("outer".fomeazon = "inner".fomeazon)
-> Index Scan using idx_t_me30_mertido2 on t_me30
(cost=0.00..6.02 rows=1 width=46)
Index Cond: ((mertido ~>=~ '2003-12-17'::bpchar) AND (mertido
~<~ '2003-12-18'::bpchar))
Filter: ((mertido ~~ '2003-12-17%'::text) AND (ertektipus =
'+MW'::bpchar))
-> Seq Scan on t_fome (cost=0.00..1.80 rows=1 width=4)
Filter: (lower((inuse)::text) = 'igen'::text)

Thats it! Thank you very much!

-- Csaba

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Együd Csaba
> Sent: 2004. január 7. 20:17
> To: 'Tom Lane'
> Cc: 'Pgsql-General(at)Postgresql(dot)Org (E-mail)'
> Subject: Re: [GENERAL] 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.
> >
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>
>
> -- 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.
>