Thread: Schedule DB Query

Schedule DB Query

From

Sergio Chavarria

Date:

17 April 2009, 01:00:42

Hi, i hope someone can help me in this mailing list. Is there a way to schedule databases querys with postgresql?? Ideally i would like to have a database table with one field containing database queries and then create a "postgres cron" for executing all my queries at a specified time.

I know one possible solution would be to create a cron in unix but i think it would be better if i can create it within the database so, if i change my system, i can keep this information just doing a "pgdump" and restoring in another system.

Maybe i am dreaming, jeje, but i want to hear the expert voices

Thank you in advance

Sergio

Re: Schedule DB Query

From

Jaume Sabater

Date:

17 April 2009, 07:26:48

On Fri, Apr 17, 2009 at 3:00 AM, Sergio Chavarria
<sergio.chavarria@gmail.com> wrote:

> Hi, i hope someone can help me in this mailing list. Is there a way to
> schedule databases querys with postgresql?? Ideally i would like to have a
> database table with one field containing database queries and then create a
> "postgres cron"  for executing all my queries at a specified time.
>
> I know one possible solution would be to create a cron in unix but i think
> it would be better if i can create it within the database so, if i change my
> system, i can keep this information just doing a "pgdump" and restoring  in
> another system.

I would go for a cron job that calls a script that launches a number
of SQL commands through psql -c or pgsql -f.

--
Jaume Sabater
http://linuxsilo.net/

"Ubi sapientas ibi libertas"

Re: Schedule DB Query

From

Guillaume Lelarge

Date:

17 April 2009, 07:56:22

Le vendredi 17 avril 2009 à 09:26:41, Jaume Sabater a écrit :
> On Fri, Apr 17, 2009 at 3:00 AM, Sergio Chavarria
>
> <sergio.chavarria@gmail.com> wrote:
> > Hi, i hope someone can help me in this mailing list. Is there a way to
> > schedule databases querys with postgresql?? Ideally i would like to have
> > a database table with one field containing database queries and then
> > create a "postgres cron"  for executing all my queries at a specified
> > time.
> >
> > I know one possible solution would be to create a cron in unix but i
> > think it would be better if i can create it within the database so, if i
> > change my system, i can keep this information just doing a "pgdump" and
> > restoring  in another system.
>
> I would go for a cron job that calls a script that launches a number
> of SQL commands through psql -c or pgsql -f.
>

You can also use pgagent to ease the creation of batch jobs (SQL or shell
script). See http://www.pgadmin.org/docs/dev/pgagent.html for more
information.


--
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

Strange behaviour with a query

From

Iñigo Martinez Lasala

Date:

17 April 2009, 08:51:59

In a production enviroment over Postgresql 8.2.13, we have found some queries take forever without finish until statement_timeout enters in play.
These queries are like this one:

SELECT ART.ART_ID, DIS.DIS_HORAS, ART.TIP_ID FROM ARTICULO ART,DISPONIBILIDAD DIS, ENTE ENT, PARTICIPACION PAR,CANCION C WHERE (ART.ART_APROBADO = 1) 
AND ((ART.ART_SHOW_ANYWAY = 1) OR (ART.ART_STOCK_ALMACEN_CENTRAL >=1) OR (ART.ART_STOCK_LOCAL  >=1) OR (ART.ART_STOCK_LOCAL_2 >= 1)  OR (ART.ART_STOCK >= 1)) 
AND (ART_DESCATALOGADO != 1) AND (ART_BUYABLE = 1) AND (NVL(ART.ART_PRECIO_FNAC_WEB,0) > 0) AND (ART.ART_PMP is not null) AND ART.TIP_ID =1  AND 
( (ENT.ent_nombre_search_string % 'COS') ) AND PAR.ROL_ID = 100004  AND PAR.ENT_ID = ENT.ENT_ID AND PAR.ART_ID = ART.ART_ID  AND ( (c.can_nombre_search_string % 'WHAT') ) 
AND C.ART_ID = ART.ART_ID  AND DIS.DIS_ID = ART.DIS_ID  AND ART.PRE_ID = 5  ORDER BY DIS.DIS_HORAS, ART_FECHA_UP DESC LIMIT 100;

Explain:
"Limit  (cost=2213.67..2213.67 rows=1 width=40)"
"  ->  Sort  (cost=2213.67..2213.67 rows=1 width=40)"
"        Sort Key: dis.dis_horas, art.art_fecha_up"
"        ->  Nested Loop  (cost=47.44..2213.66 rows=1 width=40)"
"              Join Filter: (dis.dis_id = art.dis_id)"
"              ->  Nested Loop  (cost=47.44..2212.39 rows=1 width=40)"
"                    ->  Nested Loop  (cost=47.44..2211.89 rows=1 width=52)"
"                          ->  Nested Loop  (cost=0.00..2160.43 rows=1 width=64)"
"                                ->  Index Scan using ix_pre_id on articulo art  (cost=0.00..2134.34 rows=3 width=40)"
"                                      Index Cond: (pre_id = 5::numeric)"
"                                      Filter: ((art_aprobado = 1::numeric) AND ((art_show_anyway = 1::numeric) OR (art_stock_almacen_central >= 1::numeric) OR (art_stock_local >= 1::numeric) OR (art_stock_local_2 >= 1::numeric) OR (art_stock >= 1::numeric)) AND (art_descatalogado <> 1::numeric) AND (art_buyable = 1::numeric) AND (nvl(art_precio_fnac_web, 0::numeric) > 0::numeric) AND (art_pmp IS NOT NULL) AND (tip_id = 1::numeric))"
"                                ->  Index Scan using rel_participacion_articulo_fk on participacion par  (cost=0.00..8.68 rows=1 width=24)"
"                                      Index Cond: (par.art_id = art.art_id)"
"                                      Filter: (rol_id = 100004::numeric)"
"                          ->  Bitmap Heap Scan on cancion c  (cost=47.44..51.45 rows=1 width=12)"
"                                Recheck Cond: ((c.art_id = art.art_id) AND ((c.can_nombre_search_string)::text % 'NOMBRE'::text))"
"                                ->  BitmapAnd  (cost=47.44..47.44 rows=1 width=0)"
"                                      ->  Bitmap Index Scan on rel_dis_can_fk  (cost=0.00..5.56 rows=163 width=0)"
"                                            Index Cond: (c.art_id = art.art_id)"
"                                      ->  Bitmap Index Scan on cancion_nombre_search_string_trgm  (cost=0.00..41.47 rows=650 width=0)"
"                                            Index Cond: ((can_nombre_search_string)::text % 'NOMBRE'::text)"
"                    ->  Index Scan using ix_ent_id on ente ent  (cost=0.00..0.48 rows=1 width=12)"
"                          Index Cond: (par.ent_id = ent.ent_id)"
"                          Filter: ((ent_nombre_search_string)::text % 'COS'::text)"
"              ->  Seq Scan on disponibilidad dis  (cost=0.00..1.12 rows=12 width=20)"



If I slightly change it by removing one of the conditions for CANCION table, it works an finish in miliseconds:

SELECT ART.ART_ID, DIS.DIS_HORAS, ART.TIP_ID FROM ARTICULO ART,DISPONIBILIDAD DIS, ENTE ENT, PARTICIPACION PAR,CANCION C WHERE (ART.ART_APROBADO = 1) 
AND ((ART.ART_SHOW_ANYWAY = 1) OR (ART.ART_STOCK_ALMACEN_CENTRAL >=1) OR (ART.ART_STOCK_LOCAL  >=1) OR (ART.ART_STOCK_LOCAL_2 >= 1)  OR (ART.ART_STOCK >= 1)) 
AND (ART_DESCATALOGADO != 1) AND (ART_BUYABLE = 1) AND (NVL(ART.ART_PRECIO_FNAC_WEB,0) > 0) AND (ART.ART_PMP is not null) AND ART.TIP_ID =1  AND 
( (ENT.ent_nombre_search_string % 'COS') ) AND PAR.ROL_ID = 100004  AND PAR.ENT_ID = ENT.ENT_ID AND PAR.ART_ID = ART.ART_ID  AND ( (c.can_nombre_search_string % 'WHAT') ) 
AND C.ART_ID = ART.ART_ID AND DIS.DIS_ID = ART.DIS_ID  AND ART.PRE_ID = 5  ORDER BY DIS.DIS_HORAS, ART_FECHA_UP DESC LIMIT 100;

SELECT ART.ART_ID, DIS.DIS_HORAS, ART.TIP_ID FROM ARTICULO ART,DISPONIBILIDAD DIS, ENTE ENT, PARTICIPACION PAR,CANCION C WHERE (ART.ART_APROBADO = 1) 
AND ((ART.ART_SHOW_ANYWAY = 1) OR (ART.ART_STOCK_ALMACEN_CENTRAL >=1) OR (ART.ART_STOCK_LOCAL  >=1) OR (ART.ART_STOCK_LOCAL_2 >= 1)  OR (ART.ART_STOCK >= 1)) 
AND (ART_DESCATALOGADO != 1) AND (ART_BUYABLE = 1) AND (NVL(ART.ART_PRECIO_FNAC_WEB,0) > 0) AND (ART.ART_PMP is not null) AND ART.TIP_ID =1  AND 
( (ENT.ent_nombre_search_string % 'COS') ) AND PAR.ROL_ID = 100004  AND PAR.ENT_ID = ENT.ENT_ID AND PAR.ART_ID = ART.ART_ID  AND ( (c.can_nombre_search_string % 'WHAT') ) 
AND C.ART_ID = ART.ART_ID AND DIS.DIS_ID = ART.DIS_ID  AND ART.PRE_ID = 5  ORDER BY DIS.DIS_HORAS, ART_FECHA_UP DESC LIMIT 100;


I have tried to VACUUM / ANALYZE and RECREATE indexes, change search string, but problem remains. If I launch a single select I also works:
SELECT * FROM CANCION C WHERE ( (c.can_nombre_search_string % 'WHAT') ) and ART_ID=223212;

Does anybody have an idea about what could be happening? With postgres 8.1.15 there was no problem with this query...

Thanks in advance.

Re: Strange behaviour with a query

From

Iñigo Martinez Lasala

Date:

17 April 2009, 09:00:56

Hi everybody again.

Deleting rel_dis_can_fk index has solved the problem! But.... why??

-----Original Message-----
From: Iñigo Martinez Lasala <imartinez@vectorsf.com>
To: pgsql-admin <pgsql-admin@postgresql.org>
Cc: Jaume Sabater <jsabater@gmail.com>, Sergio Chavarria <sergio.chavarria@gmail.com>
Subject: Strange behaviour with a query
Date: Fri, 17 Apr 2009 10:44:29 +0200

In a production enviroment over Postgresql 8.2.13, we have found some queries take forever without finish until statement_timeout enters in play.
These queries are like this one:

SELECT ART.ART_ID, DIS.DIS_HORAS, ART.TIP_ID FROM ARTICULO ART,DISPONIBILIDAD DIS, ENTE ENT, PARTICIPACION PAR,CANCION C WHERE (ART.ART_APROBADO = 1) 
AND ((ART.ART_SHOW_ANYWAY = 1) OR (ART.ART_STOCK_ALMACEN_CENTRAL >=1) OR (ART.ART_STOCK_LOCAL  >=1) OR (ART.ART_STOCK_LOCAL_2 >= 1)  OR (ART.ART_STOCK >= 1)) 
AND (ART_DESCATALOGADO != 1) AND (ART_BUYABLE = 1) AND (NVL(ART.ART_PRECIO_FNAC_WEB,0) > 0) AND (ART.ART_PMP is not null) AND ART.TIP_ID =1  AND 
( (ENT.ent_nombre_search_string % 'COS') ) AND PAR.ROL_ID = 100004  AND PAR.ENT_ID = ENT.ENT_ID AND PAR.ART_ID = ART.ART_ID  AND ( (c.can_nombre_search_string % 'WHAT') ) 
AND C.ART_ID = ART.ART_ID  AND DIS.DIS_ID = ART.DIS_ID  AND ART.PRE_ID = 5  ORDER BY DIS.DIS_HORAS, ART_FECHA_UP DESC LIMIT 100;

Explain:
"Limit  (cost=2213.67..2213.67 rows=1 width=40)"
"  ->  Sort  (cost=2213.67..2213.67 rows=1 width=40)"
"        Sort Key: dis.dis_horas, art.art_fecha_up"
"        ->  Nested Loop  (cost=47.44..2213.66 rows=1 width=40)"
"              Join Filter: (dis.dis_id = art.dis_id)"
"              ->  Nested Loop  (cost=47.44..2212.39 rows=1 width=40)"
"                    ->  Nested Loop  (cost=47.44..2211.89 rows=1 width=52)"
"                          ->  Nested Loop  (cost=0.00..2160.43 rows=1 width=64)"
"                                ->  Index Scan using ix_pre_id on articulo art  (cost=0.00..2134.34 rows=3 width=40)"
"                                      Index Cond: (pre_id = 5::numeric)"
"                                      Filter: ((art_aprobado = 1::numeric) AND ((art_show_anyway = 1::numeric) OR (art_stock_almacen_central >= 1::numeric) OR (art_stock_local >= 1::numeric) OR (art_stock_local_2 >= 1::numeric) OR (art_stock >= 1::numeric)) AND (art_descatalogado <> 1::numeric) AND (art_buyable = 1::numeric) AND (nvl(art_precio_fnac_web, 0::numeric) > 0::numeric) AND (art_pmp IS NOT NULL) AND (tip_id = 1::numeric))"
"                                ->  Index Scan using rel_participacion_articulo_fk on participacion par  (cost=0.00..8.68 rows=1 width=24)"
"                                      Index Cond: (par.art_id = art.art_id)"
"                                      Filter: (rol_id = 100004::numeric)"
"                          ->  Bitmap Heap Scan on cancion c  (cost=47.44..51.45 rows=1 width=12)"
"                                Recheck Cond: ((c.art_id = art.art_id) AND ((c.can_nombre_search_string)::text % 'NOMBRE'::text))"
"                                ->  BitmapAnd  (cost=47.44..47.44 rows=1 width=0)"
"                                      ->  Bitmap Index Scan on rel_dis_can_fk  (cost=0.00..5.56 rows=163 width=0)"
"                                            Index Cond: (c.art_id = art.art_id)"
"                                      ->  Bitmap Index Scan on cancion_nombre_search_string_trgm  (cost=0.00..41.47 rows=650 width=0)"
"                                            Index Cond: ((can_nombre_search_string)::text % 'NOMBRE'::text)"
"                    ->  Index Scan using ix_ent_id on ente ent  (cost=0.00..0.48 rows=1 width=12)"
"                          Index Cond: (par.ent_id = ent.ent_id)"
"                          Filter: ((ent_nombre_search_string)::text % 'COS'::text)"
"              ->  Seq Scan on disponibilidad dis  (cost=0.00..1.12 rows=12 width=20)"



If I slightly change it by removing one of the conditions for CANCION table, it works an finish in miliseconds:

SELECT ART.ART_ID, DIS.DIS_HORAS, ART.TIP_ID FROM ARTICULO ART,DISPONIBILIDAD DIS, ENTE ENT, PARTICIPACION PAR,CANCION C WHERE (ART.ART_APROBADO = 1) 
AND ((ART.ART_SHOW_ANYWAY = 1) OR (ART.ART_STOCK_ALMACEN_CENTRAL >=1) OR (ART.ART_STOCK_LOCAL  >=1) OR (ART.ART_STOCK_LOCAL_2 >= 1)  OR (ART.ART_STOCK >= 1)) 
AND (ART_DESCATALOGADO != 1) AND (ART_BUYABLE = 1) AND (NVL(ART.ART_PRECIO_FNAC_WEB,0) > 0) AND (ART.ART_PMP is not null) AND ART.TIP_ID =1  AND 
( (ENT.ent_nombre_search_string % 'COS') ) AND PAR.ROL_ID = 100004  AND PAR.ENT_ID = ENT.ENT_ID AND PAR.ART_ID = ART.ART_ID  AND ( (c.can_nombre_search_string % 'WHAT') ) 
AND C.ART_ID = ART.ART_ID AND DIS.DIS_ID = ART.DIS_ID  AND ART.PRE_ID = 5  ORDER BY DIS.DIS_HORAS, ART_FECHA_UP DESC LIMIT 100;

SELECT ART.ART_ID, DIS.DIS_HORAS, ART.TIP_ID FROM ARTICULO ART,DISPONIBILIDAD DIS, ENTE ENT, PARTICIPACION PAR,CANCION C WHERE (ART.ART_APROBADO = 1) 
AND ((ART.ART_SHOW_ANYWAY = 1) OR (ART.ART_STOCK_ALMACEN_CENTRAL >=1) OR (ART.ART_STOCK_LOCAL  >=1) OR (ART.ART_STOCK_LOCAL_2 >= 1)  OR (ART.ART_STOCK >= 1)) 
AND (ART_DESCATALOGADO != 1) AND (ART_BUYABLE = 1) AND (NVL(ART.ART_PRECIO_FNAC_WEB,0) > 0) AND (ART.ART_PMP is not null) AND ART.TIP_ID =1  AND 
( (ENT.ent_nombre_search_string % 'COS') ) AND PAR.ROL_ID = 100004  AND PAR.ENT_ID = ENT.ENT_ID AND PAR.ART_ID = ART.ART_ID  AND ( (c.can_nombre_search_string % 'WHAT') ) 
AND C.ART_ID = ART.ART_ID AND DIS.DIS_ID = ART.DIS_ID  AND ART.PRE_ID = 5  ORDER BY DIS.DIS_HORAS, ART_FECHA_UP DESC LIMIT 100;


I have tried to VACUUM / ANALYZE and RECREATE indexes, change search string, but problem remains. If I launch a single select I also works:
SELECT * FROM CANCION C WHERE ( (c.can_nombre_search_string % 'WHAT') ) and ART_ID=223212;

Does anybody have an idea about what could be happening? With postgres 8.1.15 there was no problem with this query...

Thanks in advance.

Re: Strange behaviour with a query

From

Scott Marlowe

Date:

17 April 2009, 10:17:45

On Fri, Apr 17, 2009 at 3:00 AM, Iñigo Martinez Lasala
<imartinez@vectorsf.com> wrote:
> Hi everybody again.
>
> Deleting rel_dis_can_fk index has solved the problem! But.... why??

Hard to say without explain analyze output.

Re: Strange behaviour with a query

From

Scott Marlowe

Date:

17 April 2009, 10:29:35

On Fri, Apr 17, 2009 at 4:17 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Fri, Apr 17, 2009 at 3:00 AM, Iñigo Martinez Lasala
> <imartinez@vectorsf.com> wrote:
>> Hi everybody again.
>>
>> Deleting rel_dis_can_fk index has solved the problem! But.... why??
>
> Hard to say without explain analyze output.
>

I'd hazard a guess that you're getting a lot more rows back from the
bitmap scan of rel_dis_can_fk than the query planner expects.  But
that's just a guess.

Re: Strange behaviour with a query

From

Scott Marlowe

Date:

17 April 2009, 10:30:25

On Fri, Apr 17, 2009 at 4:29 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Fri, Apr 17, 2009 at 4:17 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>> On Fri, Apr 17, 2009 at 3:00 AM, Iñigo Martinez Lasala
>> <imartinez@vectorsf.com> wrote:
>>> Hi everybody again.
>>>
>>> Deleting rel_dis_can_fk index has solved the problem! But.... why??
>>
>> Hard to say without explain analyze output.
>>
>
> I'd hazard a guess that you're getting a lot more rows back from the
> bitmap scan of rel_dis_can_fk than the query planner expects.  But
> that's just a guess.

Try craniking up your default stats setting and running analyze again
and seeing if it runs fast even with the index.

Re: Strange behaviour with a query

From

Iñigo Martinez Lasala

Date:

17 April 2009, 11:58:49

Explain Analyze from a second database (it's a replica). With original database and index in art_id table it never ends, like the query...:
That is, in original database only works if index rel_dis_can_fk is deleted, but in replicated one it works without problems. ¿¿¿??? Same hardware, same operating system, same postgres database version....

"Limit (cost=2312.74..2312.74 rows=1 width=40) (actual time=96.906..96.906 rows=0 loops=1)"
" -> Sort (cost=2312.74..2312.74 rows=1 width=40) (actual time=96.902..96.902 rows=0 loops=1)"
"        Sort Key: dis.dis_horas, art.art_fecha_up"
"        -> Nested Loop (cost=47.61..2312.73 rows=1 width=40) (actual time=96.885..96.885 rows=0 loops=1)"
"              Join Filter: (dis.dis_id = art.dis_id)"
"              -> Nested Loop (cost=47.61..2311.46 rows=1 width=40) (actual time=96.883..96.883 rows=0 loops=1)"
"                    -> Nested Loop (cost=0.00..2259.82 rows=1 width=52) (actual time=96.882..96.882 rows=0 loops=1)"
"                          -> Nested Loop (cost=0.00..2259.32 rows=1 width=64) (actual time=0.134..72.967 rows=1194 loops=1)"
"                                -> Index Scan using ix_pre_id on articulo art (cost=0.00..2233.39 rows=3 width=40) (actual time=0.102..51.898 rows=1175 loops=1)"
"                                      Index Cond: (pre_id = 5::numeric)"
"                                      Filter: ((art_aprobado = 1::numeric) AND ((art_show_anyway = 1::numeric) OR (art_stock_almacen_central >= 1::numeric) OR (art_stock_local >= 1::numeric) OR (art_stock_local_2 >= 1::numeric) OR (art_stock >= 1::numeric)) AND (art_descatalogado <> 1::numeric) AND (art_buyable = 1::numeric) AND (nvl(art_precio_fnac_web, 0::numeric) > 0::numeric) AND (art_pmp IS NOT NULL) AND (tip_id = 1::numeric))"
"                                -> Index Scan using rel_participacion_articulo_fk on participacion par (cost=0.00..8.63 rows=1 width=24) (actual time=0.013..0.014 rows=1 loops=1175)"
"                                      Index Cond: (par.art_id = art.art_id)"
"                                      Filter: (rol_id = 100004::numeric)"
"                          -> Index Scan using ix_ent_id on ente ent (cost=0.00..0.49 rows=1 width=12) (actual time=0.018..0.018 rows=0 loops=1194)"
"                                Index Cond: (par.ent_id = ent.ent_id)"
"                                Filter: ((ent_nombre_search_string)::text % '4 NON BLONDES'::text)"
"                    -> Bitmap Heap Scan on cancion c (cost=47.61..51.62 rows=1 width=12) (never executed)"
"                          Recheck Cond: ((c.art_id = art.art_id) AND ((c.can_nombre_search_string)::text % 'WHATS UP'::text))"
"                          -> BitmapAnd (cost=47.61..47.61 rows=1 width=0) (never executed)"
"                                -> Bitmap Index Scan on rel_dis_can_fk (cost=0.00..5.60 rows=169 width=0) (never executed)"
"                                      Index Cond: (c.art_id = art.art_id)"
"                                -> Bitmap Index Scan on cancion_nombre_search_string_trgm (cost=0.00..41.59 rows=666 width=0) (never executed)"
"                                      Index Cond: ((can_nombre_search_string)::text % 'WHATS UP'::text)"
"              -> Seq Scan on disponibilidad dis (cost=0.00..1.12 rows=12 width=20) (never executed)"
"Total runtime: 97.119 ms"

-----Original Message-----
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Iñigo Martinez Lasala <imartinez@vectorsf.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>, Jaume Sabater <jsabater@gmail.com>, Sergio Chavarria <sergio.chavarria@gmail.com>
Subject: Re: [ADMIN] Strange behaviour with a query
Date: Fri, 17 Apr 2009 04:17:40 -0600

On Fri, Apr 17, 2009 at 3:00 AM, Iñigo Martinez Lasala
<imartinez@vectorsf.com> wrote:
> Hi everybody again.
>
> Deleting rel_dis_can_fk index has solved the problem! But.... why??

Hard to say without explain analyze output.

Re: Strange behaviour with a query

From

Iñigo Martinez Lasala

Date:

17 April 2009, 12:01:31

Wow... thank you very much, Scott.

SELECT pg_stat_reset();
ANALYZE;
And everything working like a charm....

What is the reason for this to happen? Is it common?

-----Original Message-----
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Iñigo Martinez Lasala <imartinez@vectorsf.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>, Jaume Sabater <jsabater@gmail.com>, Sergio Chavarria <sergio.chavarria@gmail.com>
Subject: Re: [ADMIN] Strange behaviour with a query
Date: Fri, 17 Apr 2009 04:30:19 -0600

On Fri, Apr 17, 2009 at 4:29 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Fri, Apr 17, 2009 at 4:17 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>> On Fri, Apr 17, 2009 at 3:00 AM, Iñigo Martinez Lasala
>> <imartinez@vectorsf.com> wrote:
>>> Hi everybody again.
>>>
>>> Deleting rel_dis_can_fk index has solved the problem! But.... why??
>>
>> Hard to say without explain analyze output.
>>
>
> I'd hazard a guess that you're getting a lot more rows back from the
> bitmap scan of rel_dis_can_fk than the query planner expects.  But
> that's just a guess.

Try craniking up your default stats setting and running analyze again
and seeing if it runs fast even with the index.

Re: Strange behaviour with a query

From

Tom Lane

Date:

17 April 2009, 14:37:23

=?ISO-8859-1?Q?I=F1igo?= Martinez Lasala <imartinez@vectorsf.com> writes:
> ANALYZE;
> And everything working like a charm....

> What is the reason for this to happen? Is it common?

It sounds like you might never have ANALYZEd the database at all, or
anyway not have done so recently.  Recommended practice is to ANALYZE
at least as often as you vacuum.  Or turn on autovacuum and let it
take care of it.

            regards, tom lane